The first steps in setting up the tracker
This manual refers to the “Crypto&Stock Investment Tracker v1.0 – one category v1.0“. I will keep this page updated as newer versions are released. Learn more about this update here. If you prefer video above text then feel free to watch the video below, otherwise continue reading.
Welcome to your newly found unique investment tracker. The tracker which works for cryptocurrencies and stocks alike! 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. For example, enter different brokers and/or exchanges.
- Step 2: Go to the History sheet. Take a look at how the data has been entered. Make sure to enter cryptocurrency symbols. For example use “BTC” instead of “bitcoin”. 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 stockand 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: Inside the cryptocurrency box, you will find a list of the cryptocurrencies you own in column “I’. Fill in the slug of the cryptocurrency in column “H” and a “Symbol/Currency” in column “J” similar to the example. The data in column “H” and “J” are required for the dashboard.
- Step 5: Register at coinmarketcap and retrieve your personal API key from here. Go to extra > script editor > CoinAtlas > Add your personal coinmarketcap API key inside the function “coinMarketCap”, specifically here: “X-CMC_PRO_API_KEY”: ‘fbe6a0e0-xxxx-xxxx-xxxx-xxxxxxxxeec9′”
- Step 6: Fill in the original currency in which you purchased the stock inside the designated box for stocks.
- Step 7: 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 8: Update price data of cryptocurrencies and sector/industry for stocks in the custom menu as required
- Step 9: Optional: Turn off/on newsfeed in custom menu.
- Price data is retrieved from CoinMarketCap (CMC) through an API. There is a limit to 333/calls per day and 10000 calls/month. I expect you will not reach the free limit plan. However, if you require more, you will need to upgrade from the free plan to one of the paid ones.
- Data is extracted for the top 100 coins from CMC. If you have a coin outside the top 100, you are currently not able to track this coin. However, updates in the future will fix this.
- 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.
- 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.
- 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 tracker supports by default 200 cryptocurrencies & 200 stocks, however you can extend the formula’s to support as many stocks as you wish.
- The money-weighted-average-return (MWAR) is used to calculate your “total return” over your portfolio and the different categories (stock/crypto). 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 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.
Description of spreadsheet
The first sheet you will encounter after the Setup sheet is the Dashboard. Here, you will find a summary of essential parts of your portfolio. On the top left you can find the performance of your entire portfolio today in percentage and in absolute terms in your local currency. On the block to the right you will find the performance in absolute local values of the two categories inside your portfolio: cryptocurrencies and stocks. Next you will find the total amount your portfolio has gained (or lost) in your local currency as well as in percentage terms. The last is calculated through the money-weighted-average return as explained in “additional information”. Lastly, you will find the total value of your current positions and cash.
If you wish to view key metrics on an asset you own, simply click on the arrow next to the name of the symbol (in this case MRNA) and you will be presented with some financial metrics and a sparkline! The waterfall graph presents the unrealized amount your portfolio has gained (or lost) and the relative contribution of the assets. For example, you could have lost 90% on a stock, but if you only paid 100 dollars for that stock, then it is possible that the impact on your portfolio is still small.
Report & Summary of Report
These two sheets are used to create a report of your realized gains based on the transactional history you input in the “History sheet”. Do not change anything in these sheets. If you wish to change the data, do so in the history sheet.
In the Newsfeed sheet you can go to cell “D7” and click on the arrow and you will be presented with a drop-down list of all your stock holdings. Chose one stock holding and then click on the source of your chosing. For stocks you can chose from “Seeking Alpha” or from “Yahoo Finance”. If you are not interested in news from stocks, you can also chose to look up general news on cryptocurrency from “Cryptoninjas”, “Coindesk” or “Cointelegraph”.
The next tool available for your use is the “Financial Summary”. This sheet extract a table from finviz.com. The table provides you financial information on a specific stock. Enter in cell “C3” the symbol of the stock you are interested in. The table should update automatically. Unfortunatly, finviz only supports US stocks. A graph displaying the historical price is also present. You can change the graph by entering a different date in cell “P4” up and till cell “P10”.
Arguably the most important sheet is located in the “Positions” tab. This sheet provides you with an overview of all your current holdings and includes key info such as average cost basis, portfolio allocation, cost basis, sector, industry, market cap, price change daily, purchase cost, position value, gains or losses and portfolio return. All the data is calculated automatically based on the History sheet. If you make any changes in the History sheet then make sure to update the tracker through the CoinAtlas menu.
Analysis of portfolio
This sheet will provide you with a graphical overview of your investments which will help you in your future decision. You may gain some insights by assessing the different allocations within your portfolio and determining if re-balancing is necessary. Furthermore, you may gain various insight from the different allocations in your investment accounts, industry, sector and allocation by market cap size.
Analysis of portfolio
The final sheet on the investment tracker will provide you with an overview of all your returns over one holding period. In the drop-down list in cell “F4” you can chose whether you wish to view your realized gains or unrealized gains. In the dropdownbox located in cell “K4” you can chose which category you wish to view. An option exists to view your complete portfolio as well. The graphs in the sheet are linked dynamically, meaning they will change based on the values of the drop-down list and any changes made in the “History” sheet. The first chart will provide you with gains or losses for an individual asset, the second chart will provide you with your portfolio return. This is especially useful since one of your holdings may have lost 90% of its value, however, if you only spend a $100 dollar on that holding the impact impact on your total return might be minimal. The impact of your holdings on your return is what is displayed in this chart. The third chart will provide you with an overview of you categories and any gains or losses made in absolute numbers instead of percentages. The final charts are gauge meters displaying your total return (including all realized, unrealized gains and dividend) and the returns for cryptocurrencies and stocks.