The first steps in setting up the tracker
This manual refers to the “Stock Investment Tracker v1.0 – one category v1.0“. I will keep this page updated as newer versions are released. If you wish to learn more about this update, visit my other page. If you are interested in a description of the spreadsheet then visit this page.
Welcome to your newly found unique investment tracker. The tracker works for stocks! Not only will it track your investments portfolio for you, it will also provide you with quality tools such as relevant newsfeed and the financial summary tool. This is the only tracker you will need! No paid subscriptions are necessary. You can have it for absolutely free.
This page consists of two parts. In the first part, a description of how to set up the tracker is provided and in the second part a description is provided of all the essential sheets. The goal is that every user is capable of using the spreadsheet optimally.
In the setup sheet, perform the following actions, in these order:
- Step 1: Fill in your data in the account box (maximum up to thirty).
- Step 2: Go to the History sheet. Take a look at how the data has been entered. Then delete column A up and till E. Leave column “F”. This column automatically calculates the average price based on your total purchase costs and the amount of shares. Then delete column “G” and “H”. Now fill in the history sheet with your transaction history. Your broker and/or exchange often provides this data. Add any fee’s to the total purchase cost. Your transaction history is sorted based on type of stock/crypto and date to the right of the instruction box. Do not change any data here.
- Step 3: Fill in the currency you wish as output for your portfolio in the “Main currency” box. For example, choose “USD” if from the United States or “EUR” if from Europe.
- Step 4: Fill in the original currency in which you purchased the stock inside the designated box for stocks.
- Step 5: Click on the “Create menu” button inside the Setup sheet. Give permission. Then go to the menu “CoinAtlas”. Press “Build Report”. Now you have an overview of your realized gains in the sheet “Report” which is summarised inside “Summary of RG Report”.
- Step 6: Update sector & industry as necessary from the custom menu
- Step 7: Optional: Turn off/on newsfeed in custom menu.
- Stocks and currencies must be written in such a manner that google finance can find them. Write the stock exchange before the symbol if you receive the incorrect price, for example NYSE:GME instead of GME.
- The google finance functions updates data for stocks every 20 minutes.
- The total purchase costs in the history sheet must assume all costs including broker fees and transactional costs.
- The function “Build report” is a script which automatically calculates howmany shares you own of every asset and the average price you paid for it. If you sell a certain asset, it will use the FIFO method to sell the asset. If you no longer own any shares of the asset, it will be deleted automatically from the sheet “Positions”. It is possible to use this to aid you in creating your tax report. However, everything is still in beta, so proceed with caution as spreadsheet is not desgined to do taxes for you.
- Once you click on the “Build report”, everything inside “Positions” should be calculated automatically.
- The main output currency must be written in such a manner that “Googlefinance” can find it. Test it first in a different cell to prevent errors.
- The data on industry and sector is extracted from fidelity.
- The money-weighted-average-return (MWAR) is used to calculate your “total return” over your portfolio. This formula looks at the cash inflows and cash outflows, while taking into account the current value of your portfolio. It also looks at the dates you made contributions. Make sure all these data are correct in order to have an accurate total return.
- If your portfolio is less than one year old, the MWAR will give you back an inflated number. For example if you made 10% return in one month, it will return 120%, because it will assume that you keep making this return. Learn more about the MWAR here.
- If the spreadsheet is too large or too small for your screen, go to “view” and change the “zoom”. For example I use 135% zoom as I have a 2k screen and 27 inch screen.
- The tracker supports by default 200 stocks, however you can extend the formula’s to support as many stocks as you wish.
- The tracker supports by default 30 accounts, however it is possible to increase this if required. You can extend the formula’s yourself or send me a message.
- The script which calculates realized gains is limited to 30 seconds by Google (any script is). If you have an extremely large transactional history, consider cutting it in pieces.