A dynamic stock & ETF portfolio tracker in Google Sheets. It uses Google Apps Script and the EODHD API for real-time data, performance analysis, and value simulation.
To use the automated price-fetching features, you need a personal API key from EOD Historical Data.
- Get Your Key: Go to the EOD Historical Data website and register for a new account. The free plan is sufficient for this spreadsheet's functionality. Once registered, navigate to your account settings or dashboard to find your private API token. It will be a long string of letters and numbers.
- Insert the Key: Copy your API token. In the Google Sheet, go to the tab named ApiKey and paste the token directly into cell B1. The scripts will now be able to authenticate and retrieve data automatically.
This sheet appears to hold the core investment data, including details on various ETFs/stocks, their target and current portfolio compositions, invested values, and performance metrics like value variation.
This sheet is designed to provide a summary view of your portfolio, likely including visualizations for stock allocations and value variation analysis.
This tab is a financial simulator that projects the growth of an investment portfolio.
It takes a fixed monthly investment amount and distributes it across various ETFs according to a target allocation strategy. Using an assumed annual interest rate for each fund, it calculates the portfolio's future value year by year.
Its key feature is a direct comparison between the projected investment growth and a baseline of simply saving the same money, clearly illustrating the long-term effect of compound interest.
The calculation of this simulator are based on the script from Systematic_investment_plan_ROI_calculator
This spreadsheet is enhanced with powerful automation capabilities through the use of Google Apps Script and the EODHD API.
EODHD API: This is a financial data API that provides real-time and historical stock market data. The Google Apps Script in this spreadsheet makes calls to the EODHD API to fetch the latest price data for the stocks and ETFs in your portfolio.
Functionality: The integrated App Script GETETFPRICEBEFOREONDATE_EOD, retrieves the historical end-of-day closing price for a specific stock or ETF ticker.
- Inputs: It takes a ticker symbol and a dateInput.
- Process: It calls the EOD Historical Data API, searching within a 10-day window leading up to the specified date. This method cleverly finds the most recent available trading day's price, automatically handling weekends and holidays.
- Output: It returns the closing price for that day. It also includes error handling for invalid tickers (404 error) or if the requested date is more than a year in the past.
Historical Data Limit: The free subscription provides only a 1-year historical lookback. The script will intentionally return an error for dates older than one year to prevent API issues.
Daily Request Limit: The free plan is limited to 20 API calls per day. If you have many transactions or refresh the sheet too often, the API will stop responding until the next day.