If you own stock, crypto or you invest in index funds I’m sure you want to keep track of your portfolio performance. There might be some apps out there that can do the job for you, but sometimes they don’t have all the features you need.
Google Spreadsheets are a powerful tool for the task because they are versatile, easy to use and can be superpowered with Google Apps Script.
Populating the sheet with your portfolio
The first step is to structure the information of what you own into rows and columns. I like to separate all investments in a sheet per category.
In this sheet we will create 4 columns.
Company: The name of the company you own stock from.
Code: The code the company is represented within the stock market.
Titles: Number of titles you own from that company.
Value: This field will be automatically calculated from the current value of the company and the number of titles you own.
In this sheet we will create 4 columns. There is no GoogleFinance formula for index funds so we will have to get updated values manually.
Fund: Name of the index fund.
ID: Morningstar ID code of the index fund (you can get it by searching for the fund in https://www.morningstar.co.uk/ and taking the last string from the URL.
Shares: Number of shares you own.
Value: Value of each share. This field will be automatically generated from the current value of the index fund using the morningstar webpage.
=index(split(index(importhtml(concat("https://www.morningstar.es/es/funds/snapshot/snapshot.aspx?id=";B2);"table";4); 2; 3); " "); 1; 2)
Amount: This value will be the result of multiplying the number of shares you own by the automatically calculated value. (Value * Shares)
In this sheet we will create 3 columns.
Coin: The name of the currency you own.
Amount: The amount you own.
Price: The current price of what you own. This field will be automatically calculated from the current value of the coin.
At the top of each sheet on a new column we will automatically calculate the total amount we have invested.
We will finally create a new Sheet called Dashboard, with three columns, one for each of our investments, and will reference the total amounts in the other sheets.
Done! Now you have a fancy dashboard where you can check your updated portfolio at anytime and from anywhere.
Take a look at the live verison of the Spreadsheet I’m using as an example.