First load 1 company per sector.
Need a company record then main equity security for that company.
Using WMT as an example company build will be from Yahoo finance API.
Will build the load program in steps.
Set up Database table structure for the company.
The next step will be to create the structure in SQL.
CREATE TABLE company ( Ticker varchar(20), LEI varchar(50), Comp_Name varchar(50), Comp_Street varchar(50), Comp_City varchar(50), Comp_State varchar(2), Comp_Zip varchar(20), Comp_Country varchar(20), Sector varchar(50), Industry varchar(50), PRIMARY KEY(Ticker));
Now we need to identify the fields needed
=========================================================
import yfinance as yf
# Initialize the ticker
ticker = yf.Ticker("WMT")
# Get the company profile info
info = ticker.info
# List of specific fields you requested
fields = [
"longName", "address1",
"city", "state", "zip","country", "sector", "industry"
]
print("--- Company Classification & Location ---")
for field in fields:
# .get() is safer than info[field] because it won't
# throw an error if the field is missing from the API
value = info.get(field, "N/A")
print(f"{field}: {value}")
====================================================
Results:
john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$ python3 comp.py
--- Company Classification & Location ---
longName: Walmart Inc.
address1: 1 Customer Drive
city: Bentonville
state: AR
zip: 72716
country: United States
sector: Consumer Defensive
industry: Discount Stores
john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$
Next we will build a python program to create SQL INSERT commands for this data.
main.py
========================================================
import yfinance as yf
# 1. Initialize the ticker
symbol = "WMT"
ticker = yf.Ticker(symbol)
# 2. Get the company profile info
info = ticker.info
# 3. Map the yfinance data to your SQL table columns
# We use .get(key, 'N/A') and replace single quotes with double quotes
# to prevent SQL syntax errors if a name has an apostrophe (e.g., Lowe's)
data = {
"Ticker": symbol,
"Comp_Name": str(info.get("longName", "N/A")).replace("'", "''"),
"Comp_Street": str(info.get("address1", "N/A")).replace("'", "''"),
"Comp_City": str(info.get("city", "N/A")).replace("'", "''"),
"Comp_State": str(info.get("state", "N/A")).replace("'", "''"),
"Comp_Zip": str(info.get("zip", "N/A")).replace("'", "''"),
"Comp_Country": str(info.get("country", "N/A")).replace("'", "''"),
"Sector": str(info.get("sector", "N/A")).replace("'", "''"),
"Industry": str(info.get("industry", "N/A")).replace("'", "''")
}
# 4. Construct the SQL Insert Statement
sql_template = """
INSERT INTO Company
(Ticker, Comp_Name, Comp_Street, Comp_City, Comp_State, Comp_Zip, Comp_Country, Sector, Industry)
VALUES
('{Ticker}', '{Comp_Name}', '{Comp_Street}', '{Comp_City}', '{Comp_State}', '{Comp_Zip}', '{Comp_Country}', '{Sector}', '{Industry}');
"""
# Format the template with our data
sql_command = sql_template.format(**data)
print(sql_command)
=====================================================
Results:
john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$ python3 insert.py
INSERT INTO Company
(Ticker, Comp_Name, Comp_Street, Comp_City, Comp_State, Comp_Zip, Comp_Country, Sector, Industry)
VALUES
('WMT', 'Walmart Inc.', '1 Customer Drive', 'Bentonville', 'AR', '72716', 'United States', 'Consumer Defensive', 'Discount Stores');
—------------------------------------------------------------------------------------------
Now we put it all together to create a program that will directly connect to the database and execute the insert.
====================================================
import yfinance as yf
import sqlalchemy
from google.cloud.sql.connector import Connector
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:financial",
"pymysql",
user="john",
password="uconnstamford",
db="trading",
enable_iam_auth=False
)
return conn
# Create the engine pool
pool = sqlalchemy.create_engine(
"mysql+pymysql://",
creator=getconn,
pool_pre_ping=True,
)
# --- SECTION 2: DATA FETCHING & CLEANING ---
def get_ticker_data(symbol):
ticker = yf.Ticker(symbol)
info = ticker.info
# Map and sanitize data (escaping single quotes for SQL)
return {
"Ticker": symbol,
"Comp_Name": str(info.get("longName", "N/A")).replace("'", "''"),
"Comp_Street": str(info.get("address1", "N/A")).replace("'", "''"),
"Comp_City": str(info.get("city", "N/A")).replace("'", "''"),
"Comp_State": str(info.get("state", "N/A")).replace("'", "''"),
"Comp_Zip": str(info.get("zip", "N/A")).replace("'", "''"),
"Comp_Country": str(info.get("country", "N/A")).replace("'", "''"),
"Sector": str(info.get("sector", "N/A")).replace("'", "''"),
"Industry": str(info.get("industry", "N/A")).replace("'", "''")
}
# --- SECTION 3: EXECUTION ---
try:
# 1. Get the data for Walmart
company_data = get_ticker_data("WMT")
# 2. Define the SQL Template
sql_template = """
INSERT INTO company
(Ticker, Comp_Name, Comp_Street, Comp_City, Comp_State, Comp_Zip, Comp_Country, Sector, Industry)
VALUES
(:Ticker, :Comp_Name, :Comp_Street, :Comp_City, :Comp_State, :Comp_Zip, :Comp_Country, :Sector, :Industry);
"""
# 3. Connect and Execute
with pool.connect() as db_conn:
# We use sqlalchemy.text and pass the dictionary directly to prevent injection
db_conn.execute(sqlalchemy.text(sql_template), company_data)
# Commit the transaction to save changes
db_conn.commit()
print(f"Successfully inserted data for {company_data['Ticker']}")
except Exception as e:
print(f"An error occurred: {e}")
finally:
# Clean up the connector resources
connector.close()
====================================================
This examples needs a requirements file
requirements.txt
====================================================
google-cloud-sql-connector==1.15.0
sqlalchemy==2.0.25
pymysql==1.1.0
cryptography==42.0.2
====================================================
Results:
john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$ python3 main.py
Successfully inserted data for WMT
Now we can check for the company record in the database trading
Welcome to Cloud Shell! Type "help" to get started, or type "gemini" to try prompting with Gemini CLI.
Your Cloud Platform project in this session is set to sentiment-analysis-379200.
Use `gcloud config set project [PROJECT_ID]` to change to a different project.
john_iacovacci1@cloudshell:~ (sentiment-analysis-379200)$ gcloud sql connect financial --user=root --quiet
Starting Cloud SQL Proxy: [/usr/bin/cloud-sql-proxy sentiment-analysis-379200:us-east1:financial --port 9470]
2026/03/03 20:21:31 Authorizing with Application Default Credentials
2026/03/03 20:21:31 [sentiment-analysis-379200:us-east1:financial] Listening on 127.0.0.1:9470
2026/03/03 20:21:31 The proxy has started successfully and is ready for new connections!
Connecting to database with SQL user [root].Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 514
Server version: 8.0.43-google (Google)
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use trading;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from company;
+--------+------+-----------------------+----------------------------+-------------+------------+------------+---------------+--------------------+-------------------------------------+
| Ticker | LEI | Comp_Name | Comp_Street | Comp_City | Comp_State | Comp_Zip | Comp_Country | Sector | Industry |
+--------+------+-----------------------+----------------------------+-------------+------------+------------+---------------+--------------------+-------------------------------------+
| CAT | NULL | Caterpillar Inc. | 5205 N. O'Connor Boulevard | Irving | TX | 75039 | United States | Industrials | Farm & Heavy Construction Machinery |
| MSFT | NULL | Microsoft Corporation | One Microsoft Way | Redmond | WA | 98052-6399 | United States | Technology | Software - Infrastructure |
| WMT | NULL | Walmart Inc. | 1 Customer Drive | Bentonville | AR | 72716 | United States | Consumer Defensive | Discount Stores |
+--------+------+-----------------------+----------------------------+-------------+------------+------------+---------------+--------------------+-------------------------------------+
3 rows in set (0.01 sec)
mysql>
Now lets add the security for WMT
See the page on OpenFIGI
Now we have the company record created we can use the program below to create a security record associated with that company.
======================================================
#!/usr/bin/env python3.12
import json
import urllib.request
import urllib.parse
import os
import sqlalchemy
from google.cloud.sql.connector import Connector
# --- Configuration ---
OPENFIGI_API_KEY = os.environ.get("OPENFIGI_API_KEY", None)
OPENFIGI_BASE_URL = "https://api.openfigi.com"
# --- Database Setup (from Second Program) ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:financial",
"pymysql",
user="john",
password="uconnstamford",
db="trading",
enable_iam_auth=False
)
return conn
# Create the SQLAlchemy engine
pool = sqlalchemy.create_engine(
"mysql+pymysql://",
creator=getconn,
pool_pre_ping=True,
)
# --- Function 1: The API Handler ---
def api_call(path: str, data: list | None = None, method: str = "POST"):
headers = {"Content-Type": "application/json"}
if OPENFIGI_API_KEY:
headers |= {"X-OPENFIGI-APIKEY": OPENFIGI_API_KEY}
request = urllib.request.Request(
url=urllib.parse.urljoin(OPENFIGI_BASE_URL, path),
data=data and bytes(json.dumps(data), encoding="utf-8"),
headers=headers,
method=method,
)
with urllib.request.urlopen(request) as response:
return json.loads(response.read().decode("utf-8"))
# --- Function 2: Database Loader ---
def load_to_db(records):
"""Executes the inserts into the Cloud SQL database."""
if not records:
return
# Using sqlalchemy.text for parameterized queries (safer than string formatting)
insert_stmt = sqlalchemy.text("""
INSERT INTO security (FIGI, NAME, TICKER, ExchangeCd, compositeFIGI, SecurityType, MarketSector, FIGIComposite, ShareClass)
VALUES (:figi, :name, :ticker, :exchCode, :compositeFIGI, :securityType, :marketSector, :compositeFIGI_2, :shareClassFIGI)
""")
try:
with pool.connect() as db_conn:
for record in records:
# Prepare data mapping, handling N/As
params = {
"figi": record.get("figi", "N/A"),
"name": record.get("name", "N/A"),
"ticker": record.get("ticker", "N/A"),
"exchCode": record.get("exchCode", "N/A"),
"compositeFIGI": record.get("compositeFIGI", "N/A"),
"securityType": record.get("securityType", "N/A"),
"marketSector": record.get("marketSector", "N/A"),
"compositeFIGI_2": record.get("compositeFIGI", "N/A"),
"shareClassFIGI": record.get("shareClassFIGI", "N/A")
}
db_conn.execute(insert_stmt, params)
# Commit the transaction
db_conn.commit()
print(f"Successfully loaded {len(records)} records to the database.")
except Exception as e:
print(f"Database Error: {e}")
# --- Main Execution ---
def main():
mapping_request = [
{"idType": "TICKER", "idValue": "WMT", "exchCode": "US"}
]
print("-- Fetching Data from OpenFIGI --")
try:
mapping_response = api_call("/v3/mapping", mapping_request)
all_records = []
for response_item in mapping_response:
if "error" in response_item:
print(f"OpenFIGI Error: {response_item['error']}")
continue
results = response_item.get("data", [])
all_records.extend(results)
if all_records:
print(f"-- Loading {len(all_records)} results to Cloud SQL --")
load_to_db(all_records)
else:
print("No data found to load.")
except Exception as e:
print(f"An unexpected error occurred: {e}")
finally:
connector.close()
if __name__ == "__main__":
main()
===================================================
Results:
john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$ python3 main.py
-- Fetching Data from OpenFIGI --
-- Loading 1 results to Cloud SQL --
Successfully loaded 1 records to the database.
john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$
Use `gcloud config set project [PROJECT_ID]` to change to a different project.
john_iacovacci1@cloudshell:~ (sentiment-analysis-379200)$ gcloud sql connect financial --user=root --quiet
Starting Cloud SQL Proxy: [/usr/bin/cloud-sql-proxy sentiment-analysis-379200:us-east1:financial --port 9470]
2026/03/03 21:36:57 Authorizing with Application Default Credentials
2026/03/03 21:36:57 [sentiment-analysis-379200:us-east1:financial] Listening on 127.0.0.1:9470
2026/03/03 21:36:57 The proxy has started successfully and is ready for new connections!
Connecting to database with SQL user [root].Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2628
Server version: 8.0.43-google (Google)
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use trading;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from security;
+--------------+----------------+--------+------------+---------------+--------------+--------------+---------------+--------------+
| FIGI | NAME | TICKER | ExchangeCd | CompositeFIGI | SecurityType | MarketSector | FIGIComposite | ShareClass |
+--------------+----------------+--------+------------+---------------+--------------+--------------+---------------+--------------+
| BBG000BPH459 | MICROSOFT CORP | MSFT | US | BBG000BPH459 | Common Stock | Equity | BBG000BPH459 | BBG001S5TD05 |
| BBG000BWXBC2 | WALMART INC | WMT | US | BBG000BWXBC2 | Common Stock | Equity | BBG000BWXBC2 | BBG001S5XH92 |
+--------------+----------------+--------+------------+---------------+--------------+--------------+---------------+--------------+
2 rows in set (0.01 sec)
mysql>
No comments:
Post a Comment