Blogs
Stocks with Python

Stocks with Python

June 1, 2025

TradFi - Or Traditional Finances, as I saw recently on redit.

Wouldnt it be great to go out the typical google sheet and have a great UX when looking at your finances?

ℹ️
The project initially, source code - PyStocks. But then vibe coded it here 💻

If all of this sounds familiar.

It is because it is actually familiar.

Sometime ago I was doing similar Project in R Shiny 💻

About RStocksAbout RStocks
R Stock Shiny App Post
Retirement FactsRetirement Facts
Life savings and inflation in a R Shiny App.

And it was not all about yahoo finance back then…

API’s I was using with R/Stocks 📌

But this time, I thought to keep a simpler data architecture and just rely on yfinance queries.

From google sheets we have the possibility to query, very simply the price/PER/EPS of a stock:

=SPARKLINE(GoogleFinance("NYSE:KO"; "price"; HOY()-60; HOY()))
=GoogleFinance("NYSE:KO")
=SI.ERROR(GoogleFinance("NYSE:KO";"pe");999)
=GoogleFinance("NYSE:KO";"eps")

The Idea:

Business Side:

  • To feed a portfolio
  • To see consolidated information
  • To see value and dividend % CAGR for a past period
  • To let the user play with future what if scenarios (given just percentages of growth for those parameters)
  • Reply to typical questions

Tech Side:

To see whats the best way for me to authencate users: clear/supabase/logto…

PyStocks

The structure looks like so:

    • _index.md
      • _index.md
      • getting-started.md
        • _index.md
        • organize-files.md
      • _index.md
      • post-1.md

    • From the MultiChat and Aissistant Ive tried to bundle streamlit features on different pages.

    So that the main app.py can be very simple/modular and clean.

    The Data Source

    This time is going to be yfinance based. That’s it!

    Less is more

    Or so some say.

    Anyways, we will need this package: https://pypi.org/project/yfinance/

    pip install yfinance==0.2.61

    And as I dont really like building stuff on data source I dont control: I added the possibility to saved into sqlite the pulled data.

    You just pull once philosophy

    User Interaction

    The user will potentially be curious about that ‘what if’ for its assets.

    So why not giving the possibility to feed a sample Google Sheet with its sharable link to the app?

    The Charts

    I wanted to have a simple growth comparison (and actually the vibez added this cool normalized view) Normalized Value Change

    PyGWalker

    How about giving the possibility to build your own visuals?

    That’s where the pygwalker use case comes in to any streamlit app.

    AI Features for PyStocks

    This is very yolo mode.

    Do you really want to make these decions based on a LLM?

    Nooot the best idea.

    But since grok allows for real time information retrieval since not long ago, that can be very interesting to see.

    We could also chat with the pulled financial data thanks to LangChain

    Chat with a DB applied for finance - https://github.com/JAlcocerT/Data-Chat/tree/main/LangChain/PyStocksDB

    ℹ️
    I was exploring on this post and related repo, how to use LangChain as RAG with OpenAI API so that given a DB, we can use Natural Language to interact with it.

    Conclusions

    This goes few steps further than the previous project RStocks.

    About RStocksAbout RStocks
    R Stock Shiny App Post
    Python StocksPython Stocks
    Vibe coding Python WebApps

    And definitely much more than FlexDashboards in R.

    Wouldnt this be a cool companion for a website, as a lead magnet?

    Even as a subscription based model, say 7$/month to have access to such tool and historical info?

    FAQ

    Selfhostable Apps

    1. https://github.com/maybe-finance/maybe

    The personal finance app for everyone

    1. https://github.com/firefly-iii/firefly-iii

    Firefly III: a personal finances manager

    1. https://github.com/actualbudget/actual

    MIT | A local-first personal finance app

    Stock Questions - Answered

    • Stocks overperforming SP500 in xyz period / last xyz months

    • YoC when I stopped investing at a certain point of time (Dave van Knapp made a all in approach, but it can serve to see what happens after a DCA strategy)

    In the meantime, I got to understand how French amortization works (typical for mortage payments).

    See, the French amortization formula: $A = P \times \frac{r(1 + r)^n}{(1 + r)^n - 1}$.

    I also got time to compare different SP500 ETFS performance:

    Interesting Pkgs I got to learn

    the open-source Python library for data loading

    Interesting Financial Parameters / KPIs 📌

    Revenue:

    • Gross Revenue: Total income from sales.
    • Net Revenue: Gross revenue minus returns and discounts.

    Expenses:

    • Operating Expenses: Costs incurred during normal business operations.
    • COGS (Cost of Goods Sold): Direct costs of producing goods.
    • Non-operating Expenses: Costs not related to core operations (e.g., interest payments).

    Assets & Liabilities:

    • Current Assets: Cash or items convertible to cash within a year.
    • Long-term Assets: Assets held for more than a year (e.g., machinery).
    • Current Liabilities: Debts due within a year.
    • Long-term Liabilities: Debts payable over a longer period.

    Valuation Metrics:

    • EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization): Indicates operational profitability.

    • Net Cash Flow: Difference between cash inflows and outflows.

    • Debt-to-Equity Ratio: Indicates the level of financial leverage.

    • Yield: Dividend return relative to share price; calculated as Dividends per Share / Market Price per Share.

    • Payout Ratio: Proportion of earnings paid out as dividends; calculated as Dividends / Net Income.

    • PER (Price-to-Earnings Ratio): A valuation measure comparing share price to earnings per share.

    • PEGI (Price/Earnings to Growth Index): PER adjusted for expected earnings growth; calculated as PER / Annual EPS Growth.

    • PEGY (Price/Earnings to Growth and Yield): PER adjusted for both expected earnings growth and dividend yield; calculated as PER / (Annual EPS Growth + Dividend Yield).

    %%{init: {"flowchart": {"htmlLabels": false}} }%%
flowchart TD
    A["Net Cash Flow"] <--> B["Cash Inflows"]
    A <--> C["Cash Outflows"]
    B --> D["Revenue, Investments, Financing"]
    C --> E["Operating Expenses, Investments, Dividends"]
    E --> F["Dividends"]
    F --> G["Payout Ratio"]
    G --> H["Dividends / Net Income"]
    
    A -- "Positive Net Cash Flow supports" --> F
    A -- "Negative Net Cash Flow impacts ability to pay" --> F
    G -- "High Payout Ratio limits reinvestment" --> A
    Net Income vs Net Cash Flow 📌

    Net Income is not the same as Net Cash Flow.

    They are related but measure different aspects of a company’s financial health:

    Net Income:

    • Represents a company’s profit or loss during a specific period.
    • Found on the income statement.
    • Includes both cash and non-cash items:
      • Revenues minus all expenses, including non-cash expenses like depreciation and amortization.
      • Accrual-based (recognizes income/expenses when they are earned/incurred, not when cash changes hands).

    Net Cash Flow:

    • Measures the actual cash movement into and out of the company during a specific period.
    • Found on the cash flow statement.
    • Focuses on cash inflows and outflows, ignoring non-cash items!.
    • Composed of three parts:
      1. Operating Cash Flow: Cash generated from core business activities.
      2. Investing Cash Flow: Cash used in or generated by investments in assets.
      3. Financing Cash Flow: Cash used for or received from debt, equity, and dividends.
    AspectNet IncomeNet Cash Flow
    DefinitionProfit or loss from operations (accounting basis)Actual cash generated or used (cash basis)
    Non-cash itemsIncludes non-cash items (e.g., depreciation)Excludes non-cash items, tracks cash only
    TimingAccrual-based accountingTracks when cash is received or spent
    UseMeasures profitabilityMeasures liquidity and financial flexibility

    Relationship: Net Income is often the starting point for calculating Operating Cash Flow.

    Adjustments are made to add back non-cash expenses and account for changes in working capital.

    Both metrics are important:

    • Net Income: Indicates profitability.
    • Net Cash Flow: Shows the company’s ability to generate cash and stay solvent.

    A diagram is worth more than xyz words, right?

    %%{init: {"flowchart": {"htmlLabels": false}} }%%
flowchart TD
    A["Company Financials"] --> B["Revenue"]
    A --> C["Expenses"]
    A --> D["Assets & Liabilities"]
    A --> E["Valuation Metrics"]

    B --> F["Gross Revenue"]
    B --> G["Net Revenue"]
    
    C --> H["Operating Expenses"]
    C --> I["Cost of Goods Sold (COGS)"]
    C --> J["Non-operating Expenses"]
    
    D --> K["Current Assets"]
    D --> L["Long-term Assets"]
    D --> M["Current Liabilities"]
    D --> N["Long-term Liabilities"]
    
    B --> O["`**EBITDA**`"]
    C --> O
    O --> Z["Revenue - Operating Expenses"]

    B --> P["`**Net Cash Flow**`"]
    C --> P
    P --> AA["Cash Inflows - Cash Outflows"]
    
    G --> Q["`**PER (Price-to-Earnings Ratio)**`"]
    Q --> BB["Market Price per Share / EPS"]
    
    D --> R["`**Debt-to-Equity Ratio**`"]
    R --> CC["Total Debt / Total Equity"]
    
    E --> S["`**Yield**`"]
    S --> DD["Dividends per Share / Market Price per Share"]
    
    E --> T["`**PEGI (Price/Earnings to Growth Index)**`"]
    T --> EE["PER / Annual EPS Growth"]
    
    E --> U["`**Payout Ratio**`"]
    U --> FF["Dividends / Net Income"]

    How to use AI LLM Agents to get posts with this program as Source

    ℹ️
    I was testing Automatic Posts creation with AIssistant here and with this related repo.

    Interesting Software for Finances

    Financial Freedom is an open-source financial management tool.

    More about Financial Freedom Software 📌

    This project addresses privacy concerns by allowing users to self-host their financial data.

    Users can run the application on any device with Docker support, enhancing control over their financial information.

    Key features:

    • Supports any bank: Integrate with various banking institutions.
    • Private synchronization: Ensures data privacy during synchronization.
    • Self-hosting: Run on AWS, Digital Ocean, or even Raspberry Pi.
    • Budgeting tools: Helps in managing cash flow and setting budgets.
    • Global currency support: Manages finances in multiple currencies.

    The project is actively being developed, inviting community involvement through contributions.

    Financial Freedom empowers users to manage finances privately and securely.

    Similar projects include Firefly III and GnuCash.

    Simple Budgeting, Powerful Results

    Interesting Financial Stories

    These are a collection of finance related posts that have been somehow interesting for me to read:

    ℹ️
    You can create an ebook like this one with AI. How? with an AIssistant

    Interesting Financial Data WebSites

    Financial Data Apps

    Outro

    Thanks to

    • Hugo Hextra Latex/Katex Syntax
    • Thanks to Streamlit/Flask for the foundations for the web/app.

    VBA vs OpenPyxl

    And all of this could have just been done in plain excel…

    What is openpyxl?

    • Python Library:
      • openpyxl is a Python library used to read and write Excel .xlsx files. This means you can use Python code to interact with Excel spreadsheets.
      • It allows you to automate tasks like:
        • Reading data from Excel files.
        • Writing data to Excel files.
        • Modifying existing Excel files (formatting, adding/removing sheets, etc.).
    • Purpose:
      • It’s designed to work with the Office Open XML format, which is the underlying structure of modern Excel files (.xlsx).

    openpyxl vs. Excel Macros (VBA)

    • Macros (VBA):
      • Excel macros are created using Visual Basic for Applications (VBA), a programming language built into Excel.
      • They are used to automate tasks within Excel itself.
      • Macros are stored within the Excel file.
    • openpyxl (Python):
      • openpyxl is a Python library that operates externally to Excel.
      • It allows you to manipulate Excel files from your Python scripts.
      • Python code is separate from the Excel file.
    • Key Differences:
      • Location: Macros live inside the Excel file; openpyxl scripts are external.
      • Language: Macros use VBA; openpyxl uses Python.
      • Functionality:
        • openpyxl excels at data manipulation, formatting, and file structure changes.
        • Macros are often used for automating repetitive tasks within the Excel user interface (e.g., button clicks, custom functions).
      • Macros and openpyxl interaction:
        • openpyxl itself does not execute VBA macros.
        • It also does not fully preserve all aspects of macro enabled excel files(.xlsm). There are limitations when attempting to save .xlsm files after they have been worked on by openpyxl.
        • However, you can use Python libraries like win32com to interact with Excel’s application object and potentially run macros. But this is a different method than openpyxl’s native functionality.
    • Can you do similar things?
      • In many cases, yes, you can achieve similar automation results with openpyxl as you would with macros.
      • For data-centric tasks, openpyxl is often more powerful and flexible due to Python’s extensive libraries.
      • For tasks that require direct interaction with the Excel user interface, macros might be more suitable.

    In summary:

    • openpyxl is a powerful tool for working with Excel files programmatically.
    • While it doesn’t directly replace VBA macros, it provides a robust alternative for many automation needs.
    SelfHosting - 1st Half 2025 June