Build Financial Trading System
First let's get sector information.
Ask Gemini
How can I get the GICS sectors and percentages for S&P500
To obtain the current Global Industry Classification Standard (GICS) sectors and their respective market-cap weight percentages for the S&P 500, you have several reliable paths depending on whether you want official indices, programmatic access via Python, or provider breakdowns.
---
### 1. Official & Provider Sources
* **S&P Dow Jones Indices:** The official creator of the index provides factsheets and methodology profiles on the [S&P DJI Sectors page](https://www.spglobal.com/spdji/en/index-family/equity/us-equity/sp-sectors/?utm_source=gemini). Monthly fact sheets for the S&P 500 list exact sector breakdowns.
* **State Street Global Advisors (SSGA):** As the manager of SPY, SSGA publishes daily weightings and constituent lists. Their [GICS Sector and Industry Map](https://www.ssga.com/us/en/institutional/capabilities/equities/sector-investing/gics-sector-and-industry-map?utm_source=gemini) is a useful tool for tracking sector-to-industry mappings and corresponding Select Sector SPDR ETFs (like XLK, XLF, XLV).
---
### 2. Programmatic Retrieval (Python)
If you are looking to pull this data automatically into an analysis pipeline or database, you can use common financial and market data packages:
* **`yfinance` / Yahoo Finance:** You can inspect major S&P 500 ETFs (such as `SPY` or `IVV`) to extract sector weight allocations via their fund data dictionary:
```python
import yfinance as yf
spy = yf.Ticker("SPY")
# Retrieve sector weightings dictionary
sectors = spy.funds_data.sector_weightings
print(sectors)
adjust python program to pull sector name then weight
import yfinance as yf
def get_sp500_sectors():
# Fetch SPY (or IVV / VOO) fund data
ticker = "SPY"
fund = yf.Ticker(ticker)
# Retrieve sector weightings dictionary
sector_weights = fund.funds_data.sector_weightings
if not sector_weights:
print(f"Sector data is currently unavailable for {ticker}.")
return
print(f"--- S&P 500 Sector Breakdown (via {ticker}) ---")
print(f"{'GICS Sector Name':<32} | {'Weight':<10}")
print("-" * 45)
# Sort sectors by weight in descending order
sorted_sectors = sorted(sector_weights.items(), key=lambda x: x[1], reverse=True)
for sector, weight in sorted_sectors:
# Format sector name (replace hyphens/underscores and capitalize)
clean_name = sector.replace("-", " ").replace("_", " ").title()
# Convert decimal weight to percentage if necessary (yfinance typically returns decimals like 0.33)
pct_weight = weight * 100 if weight <= 1.0 else weight
print(f"{clean_name:<32} | {pct_weight:6.2f}%")
if __name__ == "__main__":
get_sp500_sectors()
$ python spy.py
--- S&P 500 Sector Breakdown (via SPY) ---
GICS Sector Name | Weight
---------------------------------------------
Technology | 38.69%
Financial Services | 12.06%
Communication Services | 9.50%
Consumer Cyclical | 9.31%
Healthcare | 9.28%
Industrials | 7.75%
Consumer Defensive | 4.46%
Energy | 3.48%
Utilities | 1.98%
Realestate | 1.80%
Basic Materials | 1.68%
Use a previous database program to merge into one load program.
Gemini
change program to write to sql database using following - import sqlalchemy
import csv
from google.cloud.sql.connector import Connector
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False
)
return conn
CREATE TABLE sector ( sec_name varchar(50), sec_percentage decimal(10, 2) , sec_desc varchar(100), PRIMARY KEY(sec_name));
sector_load.py
import yfinance as yf
from google.cloud.sql.connector import Connector
from sqlalchemy import create_engine, text
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False,
)
return conn
# Create SQLAlchemy engine using the Cloud SQL connector
engine = create_engine("mysql+pymysql://", creator=getconn)
def setup_database():
"""Ensures the sector table exists matching your schema."""
create_table_query = """
CREATE TABLE IF NOT EXISTS sector (
sec_name varchar(50),
sec_percentage decimal(10, 2),
sec_desc varchar(100),
PRIMARY KEY(sec_name)
);
"""
with engine.begin() as connection:
connection.execute(text(create_table_query))
def save_sp500_sectors():
# Ensure the table is created
setup_database()
# Fetch SPY fund data
ticker = "SPY"
fund = yf.Ticker(ticker)
sector_weights = fund.funds_data.sector_weightings
if not sector_weights:
print(f"Sector data is currently unavailable for {ticker}.")
return
print(f"--- Fetching & Saving S&P 500 Sectors to Database ---")
upsert_query = text("""
INSERT INTO sector (sec_name, sec_percentage, sec_desc)
VALUES (:sec_name, :sec_percentage, :sec_desc)
ON DUPLICATE KEY UPDATE
sec_percentage = VALUES(sec_percentage),
sec_desc = VALUES(sec_desc)
""")
with engine.begin() as connection:
for sector, weight in sector_weights.items():
# Clean up names and calculate percentage
clean_name = sector.replace("-", " ").replace("_", " ").title()
pct_weight = weight * 100 if weight <= 1.0 else weight
desc = f"S&P 500 Sector Weight via {ticker}"
# Execute upsert statement
connection.execute(
upsert_query,
{
"sec_name": clean_name,
"sec_percentage": pct_weight,
"sec_desc": desc,
},
)
print(f"Saved/Updated: {clean_name:<30} | {pct_weight:6.2f}%")
print("\n--- Database sync complete! ---")
if __name__ == "__main__":
save_sp500_sectors()
$ python sector_load.py
--- Fetching & Saving S&P 500 Sectors to Database ---
Saved/Updated: Realestate | 1.80%
Saved/Updated: Consumer Cyclical | 9.31%
Saved/Updated: Basic Materials | 1.68%
Saved/Updated: Consumer Defensive | 4.46%
Saved/Updated: Technology | 38.69%
Saved/Updated: Communication Services | 9.50%
Saved/Updated: Financial Services | 12.06%
Saved/Updated: Utilities | 1.98%
Saved/Updated: Industrials | 7.75%
Saved/Updated: Energy | 3.48%
Saved/Updated: Healthcare | 9.28%
Now we ask Gemini to create a program to list sector table contents.
create a new python program to list the sector SQL table
sector_list.py
from google.cloud.sql.connector import Connector
from sqlalchemy import create_engine, text
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False,
)
return conn
# Create SQLAlchemy engine using the Cloud SQL connector
engine = create_engine("mysql+pymysql://", creator=getconn)
def list_sectors():
"""Queries and displays all rows from the sector table."""
query = text(
"SELECT sec_name, sec_percentage, sec_desc FROM sector ORDER BY"
" sec_percentage DESC;"
)
print("--- S&P 500 Sectors from Database ---")
print(
f"{'Sector Name':<30} | {'Percentage':<10} | {'Description'}"
)
print("-" * 75)
with engine.connect() as connection:
result = connection.execute(query)
rows = result.fetchall()
if not rows:
print("No records found in the sector table.")
return
for row in rows:
sec_name, sec_percentage, sec_desc = row
print(f"{sec_name:<30} | {sec_percentage:6.2f}% | {sec_desc}")
print("-" * 75)
print(f"Total sectors listed: {len(rows)}")
if __name__ == "__main__":
list_sectors()
$ python sector_list.py
--- S&P 500 Sectors from Database ---
Sector Name | Percentage | Description
---------------------------------------------------------------------------
Technology | 38.69% | S&P 500 Sector Weight via SPY
Financial Services | 12.06% | S&P 500 Sector Weight via SPY
Communication Services | 9.50% | S&P 500 Sector Weight via SPY
Consumer Cyclical | 9.31% | S&P 500 Sector Weight via SPY
Healthcare | 9.28% | S&P 500 Sector Weight via SPY
Industrials | 7.75% | S&P 500 Sector Weight via SPY
Consumer Defensive | 4.46% | S&P 500 Sector Weight via SPY
Energy | 3.48% | S&P 500 Sector Weight via SPY
Utilities | 1.98% | S&P 500 Sector Weight via SPY
Realestate | 1.80% | S&P 500 Sector Weight via SPY
Basic Materials | 1.68% | S&P 500 Sector Weight via SPY
---------------------------------------------------------------------------
Total sectors listed: 11
Next we will use cloud run process to build a strategy table with the help of Gemini
create a cloud run python program that leverages flask to build a create read update abd delete process for the sql table - CREATE TABLE strategy ( Strategy_name VARCHAR(50) NOT NULL,
Strategy_desc VARCHAR(50), Strategy_formula VARCHAR(255), PRIMARY KEY (Strategy_name));
Here is a complete Cloud Run-ready Python Flask application that provides a full CRUD (Create, Read, Update, Delete) interface for your strategy table.
It uses Bootstrap 5 for a clean, responsive web interface directly embedded into the Flask template, and leverages your Google Cloud SQL connection configuration via SQLAlchemy.
Project Directory Structure
To deploy this on Cloud Run, place these files in a single folder:
├── main.py
├── requirements.txt
└── Dockerfile
$ mkdir strategy
$ cd strategy/
import os
from flask import Flask, flash, redirect, render_template_string, request, url_for
from google.cloud.sql.connector import Connector
from sqlalchemy import create_engine, text
app = Flask(__name__)
app.secret_key = "your-secret-key-here" # Required for flash messages
# --- DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False,
)
return conn
# Create SQLAlchemy engine using the Cloud SQL connector
engine = create_engine("mysql+pymysql://", creator=getconn)
def setup_database():
"""Ensures the strategy table exists matching your schema."""
create_table_query = """
CREATE TABLE IF NOT EXISTS strategy (
Strategy_name VARCHAR(50) NOT NULL,
Strategy_desc VARCHAR(50),
Strategy_formula VARCHAR(255),
PRIMARY KEY (Strategy_name)
);
"""
with engine.begin() as connection:
connection.execute(text(create_table_query))
# Initialize table on startup
setup_database()
# --- HTML TEMPLATE (Bootstrap 5) ---
HTML_TEMPLATE = """
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Strategy Manager</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
<div class="container py-5">
<h1 class="mb-4 text-center">Trading Strategy Manager</h1>
<!-- Flash Messages -->
{% with messages = get_flashed_messages(with_categories=true) %}
{% if messages %}
{% for category, message in messages %}
<div class="alert alert-{{ 'danger' if category == 'error' else 'success' }} alert-dismissible fade show" role="alert">
{{ message }}
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
<!-- Input / Edit Form Card -->
<div class="card shadow-sm mb-5">
<div class="card-header bg-primary text-white">
<h5 class="mb-0">{{ 'Edit Strategy' if edit_item else 'Add New Strategy' }}</h5>
</div>
<div class="card-body">
<form action="{{ url_for('update_strategy') if edit_item else url_for('add_strategy') }}" method="POST">
<div class="mb-3">
<label for="strategy_name" class="form-label">Strategy Name</label>
<input type="text" class="form-control" id="strategy_name" name="strategy_name"
value="{{ edit_item.Strategy_name if edit_item else '' }}"
{{ 'readonly' if edit_item else 'required' }}>
{% if edit_item %}<div class="form-text">Primary key cannot be modified.</div>{% endif %}
</div>
<div class="mb-3">
<label for="strategy_desc" class="form-label">Description</label>
<input type="text" class="form-control" id="strategy_desc" name="strategy_desc"
value="{{ edit_item.Strategy_desc if edit_item else '' }}" maxlength="50">
</div>
<div class="mb-3">
<label for="strategy_formula" class="form-label">Formula</label>
<input type="text" class="form-control" id="strategy_formula" name="strategy_formula"
value="{{ edit_item.Strategy_formula if edit_item else '' }}" maxlength="255" required>
</div>
<button type="submit" class="btn btn-{{ 'warning' if edit_item else 'success' }}">
{{ 'Update Strategy' if edit_item else 'Save Strategy' }}
</button>
{% if edit_item %}
<a href="{{ url_for('index') }}" class="btn btn-secondary ms-2">Cancel</a>
{% endif %}
</form>
</div>
</div>
<!-- Data Table Card -->
<div class="card shadow-sm">
<div class="card-header bg-secondary text-white">
<h5 class="mb-0">Existing Strategies</h5>
</div>
<div class="card-body p-0">
<div class="table-responsive">
<table class="table table-striped table-hover mb-0 align-middle">
<thead class="table-dark">
<tr>
<th>Name</th>
<th>Description</th>
<th>Formula</th>
<th class="text-end">Actions</th>
</tr>
</thead>
<tbody>
{% if strategies %}
{% for row in strategies %}
<tr>
<td class="fw-bold">{{ row.Strategy_name }}</td>
<td>{{ row.Strategy_desc or '' }}</td>
<td><code>{{ row.Strategy_formula }}</code></td>
<td class="text-end">
<a href="{{ url_for('edit_strategy', name=row.Strategy_name) }}" class="btn btn-sm btn-outline-primary">Edit</a>
<form action="{{ url_for('delete_strategy', name=row.Strategy_name) }}" method="POST" class="d-inline" onsubmit="return confirm('Are you sure you want to delete this strategy?');">
<button type="submit" class="btn btn-sm btn-outline-danger">Delete</button>
</form>
</td>
</tr>
{% endfor %}
{% else %}
<tr>
<td colspan="4" class="text-center py-4 text-muted">No strategies found. Create one above!</td>
</tr>
{% endif %}
</tbody>
</table>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
"""
# --- FLASK ROUTES ---
@app.route("/")
def index():
with engine.connect() as conn:
result = conn.execute(
text(
"SELECT Strategy_name, Strategy_desc, Strategy_formula FROM"
" strategy ORDER BY Strategy_name;"
)
)
strategies = result.fetchall()
return render_template_string(
HTML_TEMPLATE, strategies=strategies, edit_item=None
)
@app.route("/add", methods=["POST"])
def add_strategy():
name = request.form.get("strategy_name").strip()
desc = request.form.get("strategy_desc").strip()
formula = request.form.get("strategy_formula").strip()
try:
with engine.begin() as conn:
conn.execute(
text(
"INSERT INTO strategy (Strategy_name, Strategy_desc,"
" Strategy_formula) VALUES (:name, :desc, :formula)"
),
{"name": name, "desc": desc, "formula": formula},
)
flash(f"Strategy '{name}' created successfully!", "success")
except Exception as e:
flash(f"Error creating strategy: {str(e)}", "error")
return redirect(url_for("index"))
@app.route("/edit/<path:name>")
def edit_strategy(name):
with engine.connect() as conn:
result = conn.execute(
text(
"SELECT Strategy_name, Strategy_desc, Strategy_formula FROM"
" strategy WHERE Strategy_name = :name"
),
{"name": name},
)
edit_item = result.fetchone()
res_all = conn.execute(
text(
"SELECT Strategy_name, Strategy_desc, Strategy_formula FROM"
" strategy ORDER BY Strategy_name;"
)
)
strategies = res_all.fetchall()
if not edit_item:
flash(f"Strategy '{name}' not found.", "error")
return redirect(url_for("index"))
return render_template_string(
HTML_TEMPLATE, strategies=strategies, edit_item=edit_item
)
@app.route("/update", methods=["POST"])
def update_strategy():
name = request.form.get("strategy_name")
desc = request.form.get("strategy_desc").strip()
formula = request.form.get("strategy_formula").strip()
try:
with engine.begin() as conn:
conn.execute(
text(
"UPDATE strategy SET Strategy_desc = :desc, Strategy_formula ="
" :formula WHERE Strategy_name = :name"
),
{"name": name, "desc": desc, "formula": formula},
)
flash(f"Strategy '{name}' updated successfully!", "success")
except Exception as e:
flash(f"Error updating strategy: {str(e)}", "error")
return redirect(url_for("index"))
@app.route("/delete/<path:name>", methods=["POST"])
def delete_strategy(name):
try:
with engine.begin() as conn:
conn.execute(
text("DELETE FROM strategy WHERE Strategy_name = :name"),
{"name": name},
)
flash(f"Strategy '{name}' deleted successfully!", "success")
except Exception as e:
flash(f"Error deleting strategy: {str(e)}", "error")
return redirect(url_for("index"))
if __name__ == "__main__":
# Cloud Run provides the PORT environment variable
port = int(os.environ.get("PORT", 8080))
app.run(host="0.0.0.0", port=port, debug=False)
requirements.txt
flask
gunicorn
sqlalchemy
pymysql
cloud-sql-python-connector[pymysql]
cryptography
Dockerfile
FROM python:3.11-slim
# Install system dependencies required for building certain Python packages
RUN apt-get update && apt-get install -y --no-install-recommends \
build-essential \
libffi-dev \
&& rm -rf /var/lib/apt/lists/*
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir --upgrade pip
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
# Cloud Run sets the $PORT environment variable, Gunicorn binds to it automatically
CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 --timeout 0 main:app
When we purchase or sell a stock we will generate a recording of that transaction and the result is put in a holdings file.
A transaction record and a holdings record will be generated.
Provide the table structures to gemini and ask for a standalone python program..
We will buy 100 shares of microsoft based upon the price from finance API.
stock_buy.py
from datetime import date
from google.cloud.sql.connector import Connector
from sqlalchemy import create_engine, text
import yfinance as yf
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False,
)
return conn
# Create SQLAlchemy engine using the Cloud SQL connector
engine = create_engine("mysql+pymysql://", creator=getconn)
def setup_database():
"""Ensures strategy, transactions, and holdings tables exist with proper constraints."""
with engine.begin() as connection:
# 1. Strategy Table
connection.execute(
text("""
CREATE TABLE IF NOT EXISTS strategy (
Strategy_name VARCHAR(50) NOT NULL,
Strategy_desc VARCHAR(50),
Strategy_formula VARCHAR(255),
PRIMARY KEY (Strategy_name)
);
""")
)
# 2. Transactions Table
connection.execute(
text("""
CREATE TABLE IF NOT EXISTS transactions (
transaction_id INT AUTO_INCREMENT PRIMARY KEY,
strategy VARCHAR(50),
ticker VARCHAR(10),
transaction_date DATE,
transaction_type ENUM('BUY', 'SELL'),
quantity DECIMAL(19, 4),
price_per_share DECIMAL(19, 2),
CONSTRAINT fk_transaction_strategy
FOREIGN KEY (strategy)
REFERENCES strategy(Strategy_name)
ON UPDATE CASCADE
ON DELETE CASCADE
);
""")
)
# 3. Holdings Table (Matching your exact schema layout)
connection.execute(
text("""
CREATE TABLE IF NOT EXISTS holdings (
Holding_strategy VARCHAR(50) NOT NULL,
Holdings_date DATE NOT NULL,
Holdings_ticker VARCHAR(20) NOT NULL,
purchase_price DECIMAL(12, 2),
holdings_amount INT,
purchase_date DATE,
purchase_cost DECIMAL(19, 4),
PRIMARY KEY (Holding_strategy, Holdings_date, Holdings_ticker),
CONSTRAINT fk_holdings_strategy
FOREIGN KEY (Holding_strategy)
REFERENCES strategy(Strategy_name)
ON UPDATE CASCADE
ON DELETE CASCADE
);
""")
)
def fetch_current_price(ticker_symbol):
"""Fetches the latest regular market price using yfinance."""
ticker = yf.Ticker(ticker_symbol)
price = None
try:
price = ticker.fast_info["last_price"]
except Exception:
pass
if not price:
hist = ticker.history(period="1d")
if not hist.empty:
price = hist["Close"].iloc[-1]
if not price:
raise ValueError(f"Could not retrieve market price for {ticker_symbol}")
return float(price)
def buy_msft_shares(strategy_name, quantity=100.0):
ticker_symbol = "MSFT"
# 1. Get live price from yfinance
print(f"Fetching current market price for {ticker_symbol} via yfinance...")
current_price = fetch_current_price(ticker_symbol)
print(f"Current Price for {ticker_symbol}: ${current_price:.2f}")
today_date = date.today().isoformat()
total_cost = float(quantity) * current_price
with engine.begin() as connection:
# 2. Insert into transactions table
trans_query = text("""
INSERT INTO transactions
(strategy, ticker, transaction_date, transaction_type, quantity, price_per_share)
VALUES (:strategy, :ticker, :transaction_date, :transaction_type, :quantity, :price_per_share)
""")
connection.execute(
trans_query,
{
"strategy": strategy_name,
"ticker": ticker_symbol,
"transaction_date": today_date,
"transaction_type": "BUY",
"quantity": quantity,
"price_per_share": current_price,
},
)
# 3. Insert/Update the holdings table using your exact column layout
holding_query = text("""
INSERT INTO holdings
(Holding_strategy, Holdings_date, Holdings_ticker, purchase_price, holdings_amount, purchase_date, purchase_cost)
VALUES (:strategy, :h_date, :ticker, :price, :amount, :p_date, :cost)
ON DUPLICATE KEY UPDATE
purchase_price = VALUES(purchase_price),
holdings_amount = holdings_amount + VALUES(holdings_amount),
purchase_cost = purchase_cost + VALUES(purchase_cost)
""")
connection.execute(
holding_query,
{
"strategy": strategy_name,
"h_date": today_date,
"ticker": ticker_symbol,
"price": current_price,
"amount": int(quantity),
"p_date": today_date,
"cost": total_cost,
},
)
print(
f"Successfully recorded BUY and updated Holdings for {int(quantity)}"
f" shares of {ticker_symbol} at ${current_price:.2f} under strategy"
f" '{strategy_name}'."
)
if __name__ == "__main__":
# Initialize schema constraints & tables
setup_database()
# Ensure sample strategy exists to satisfy foreign key constraints
with engine.begin() as conn:
conn.execute(
text("""
INSERT IGNORE INTO strategy (Strategy_name, Strategy_desc, Strategy_formula)
VALUES ('Growth-Tech', 'Long term mega-cap tech allocation', 'P/E < 35 & Revenue Growth > 10%')
""")
)
# Execute the buy order and populate holdings
buy_msft_shares(strategy_name="Growth-Tech", quantity=100.0)
$ python stock_buy.py
Fetching current market price for MSFT via yfinance...
Current Price for MSFT: $498.00
Successfully recorded BUY and updated Holdings for 100 shares of MSFT at $498.00 under strategy 'Growth-Tech'.
Ask gemini to build a list program for holdings and transactions
holdings_list.py
from google.cloud.sql.connector import Connector
from sqlalchemy import create_engine, text
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False,
)
return conn
# Create SQLAlchemy engine using the Cloud SQL connector
engine = create_engine("mysql+pymysql://", creator=getconn)
def list_holdings():
"""Queries and displays all rows from the holdings table."""
query = text("""
SELECT Holding_strategy, Holdings_date, Holdings_ticker,
purchase_price, holdings_amount, purchase_date, purchase_cost
FROM holdings
ORDER BY Holdings_date DESC, Holding_strategy, Holdings_ticker;
""")
print("--- Portfolio Holdings Database ---")
print(
f"{'Strategy':<18} | {'Holdings Date':<12} | {'Ticker':<8} | {'Price':<10} |"
f" {'Amount':<8} | {'Cost':<12}"
)
print("-" * 78)
with engine.connect() as connection:
result = connection.execute(query)
rows = result.fetchall()
if not rows:
print("No records found in the holdings table.")
return
for row in rows:
(
strategy,
h_date,
ticker,
price,
amount,
p_date,
cost,
) = row
# Format monetary and numerical values nicely
price_str = f"${price:,.2f}" if price is not None else "N/A"
cost_str = f"${cost:,.4f}" if cost is not None else "N/A"
amount_str = f"{amount:,}" if amount is not None else "N/A"
print(
f"{strategy:<18} | {str(h_date):<12} | {ticker:<8} |"
f" {price_str:<10} | {amount_str:<8} | {cost_str:<12}"
)
print("-" * 78)
print(f"Total holdings records: {len(rows)}")
if __name__ == "__main__":
list_holdings()
$ python holdings_list.py
--- Portfolio Holdings Database ---
Strategy | Holdings Date | Ticker | Price | Amount | Cost
------------------------------------------------------------------------------
Growth-Tech | 2026-09-22 | MSFT | $498.00 | 100 | $49,800.0000
------------------------------------------------------------------------------
Total holdings records: 1
And list the transaction file
transaction_list.py
from google.cloud.sql.connector import Connector
from sqlalchemy import create_engine, text
# --- SECTION 1: DATABASE CONNECTION SETUP ---
connector = Connector()
def getconn():
conn = connector.connect(
"sentiment-analysis-379200:us-east1:fintech",
"pymysql",
user="root",
password="Uconnstamford1!",
db="jiacovacci",
enable_iam_auth=False,
)
return conn
# Create SQLAlchemy engine using the Cloud SQL connector
engine = create_engine("mysql+pymysql://", creator=getconn)
def list_transactions():
"""Queries and displays all rows from the transactions table."""
query = text("""
SELECT transaction_id, strategy, ticker, transaction_date,
transaction_type, quantity, price_per_share
FROM transactions
ORDER BY transaction_date DESC, transaction_id DESC;
""")
print("--- Transactions Log Database ---")
print(
f"{'ID':<4} | {'Strategy':<16} | {'Ticker':<8} | {'Date':<12} |"
f" {'Type':<6} | {'Quantity':<10} | {'Price':<10}"
)
print("-" * 80)
with engine.connect() as connection:
result = connection.execute(query)
rows = result.fetchall()
if not rows:
print("No records found in the transactions table.")
return
for row in rows:
tid, strategy, ticker, t_date, t_type, quantity, price = row
# Format numerical and price values nicely
qty_str = f"{quantity:,.4f}" if quantity is not None else "N/A"
price_str = f"${price:,.2f}" if price is not None else "N/A"
print(
f"{tid:<4} | {strategy:<16} | {ticker:<8} | {str(t_date):<12} |"
f" {t_type:<6} | {qty_str:<10} | {price_str:<10}"
)
print("-" * 80)
print(f"Total transaction records: {len(rows)}")
if __name__ == "__main__":
list_transactions()
$ python transaction_list.py
--- Transactions Log Database ---
ID | Strategy | Ticker | Date | Type | Quantity | Price
--------------------------------------------------------------------------------
1 | Growth-Tech | MSFT | 2026-09-22 | BUY | 100.0000 | $498.00
--------------------------------------------------------------------------------
Total transaction records: 1
j
No comments:
Post a Comment