Stocks with Python
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?
If all of this sounds familiar.
It is because it is actually familiar.
Sometime ago I was doing similar Project in R Shiny 💻
And it was not all about yahoo finance back then…
API’s I was using with R/Stocks 📌
- PriceR
- QuantDL - API required
- QuantMod
- yfR - yahoo finance in R
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…
https://github.com/clerk/clerk-sdk-python/blob/main/README.md
https://www.reddit.com/r/Supabase/comments/1dvabn6/what_is_the_best_solution_to_use_supabase_auth/
https://www.reddit.com/r/nextjs/comments/1bvda9r/officially_hate_supabase_auth/?rdt=40537
https://www.reddit.com/r/Supabase/comments/xaxecr/authentication_with_supabase_is_easy_almost/
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)
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
Conclusions
This goes few steps further than the previous project RStocks.
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
The personal finance app for everyone
Firefly III: a personal finances manager
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:
LON:SPY5
(SPY5.L) dist usd 0.09% IE00B6YX5C33 Link to JustETF profileLON:VUAA
, acc usd 0.07% IE00BFMXXD54 Link to JustETF profile- In GSheets, you have the SP500 index under the name
INDEXSP:.INX
Interesting Pkgs I got to learn
- Data Load Tool - https://github.com/dlt-hub/dlt
the open-source Python library for data loading
- How to inspect a SQL DB with AI (without knowing much about it…)
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:
- Operating Cash Flow: Cash generated from core business activities.
- Investing Cash Flow: Cash used in or generated by investments in assets.
- Financing Cash Flow: Cash used for or received from debt, equity, and dividends.
Aspect | Net Income | Net Cash Flow |
---|---|---|
Definition | Profit or loss from operations (accounting basis) | Actual cash generated or used (cash basis) |
Non-cash items | Includes non-cash items (e.g., depreciation) | Excludes non-cash items, tracks cash only |
Timing | Accrual-based accounting | Tracks when cash is received or spent |
Use | Measures profitability | Measures 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
Interesting Software for Finances
Financial Freedom
is an open-source financial management tool.
- https://github.com/serversideup/financial-freedom
- It serves as an alternative to commercial apps like Mint and YNAB.
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:
Interesting Financial Data WebSites
https://www.multpl.com/- It has Sp500 PE Ratios, 10y treausry rates…
https://stockanalysis.com/ esta es buena para stocks, etfs no
https://www.justetf.com/uk/ esta es un screener de ETFs muy bueno, además están todas las variantes monetarias de cada ETF
https://www.digrin.com/stocks/detail/IDUS.L/
- Interesting payout ratio, dgr,…
https://divvydiary.com/en/?via=elisa&gad_source=1&gclid=Cj0KCQiA0fu5BhDQARIsAMXUBOIN8XXGkoEZZbfhufMr55Y2kSIuAGXDP4Lb1LYgLlRMnezpTUl2tkAaAp2kEALw_wcB esta la encontré el otro día, ofrece datos históricos de stocks y algún ETF (FUSD te lo da pero IDUS no), pero mola que me da el ISIN rápido, entonces veo rápidamente en qué país cotiza
https://tools.mhinvest.com/mhichart una web app que tiene una idea muy parecida a la que pensamos en su momento, pero no considera seguir metiendo gasolina, parte de una cantidad inicial y listo.
https://www.nasdaq.com/market-activity/stocks/mcd/price-earnings-peg-ratios
https://observationsandnotes.blogspot.com/2009/01/impact-of-price-to-earnings-ratios.html
https://observationsandnotes.blogspot.com/2013/03/what-will-my-bond-cd-be-worth-in-years.html
PE vs 1year returns, very interesting thought which reminds us about regression to the mean https://observationsandnotes.blogspot.com/2012/03/scatter-diagram-pe-stock-market-return.html
https://observationsandnotes.blogspot.com/2011/03/stock-market-100-year-inflation-history.html
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.