This manual refers to the basic edition of the Crypto&Stocks Investment Tracker v1.0. If you are interested in one of the different guides for the premium trackers, please visit this page (or chose from the menu).
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. You can have this version for absolutely free. For most investors, this version provides them with all the tools they need! However, if you are searching for more features such as networth tracking and having an overview of your realized gains (FIFO/LIFO), then check out the premium trackers.
This page consists of two parts. The first part provides a description of how to set up the tracker and the second part describes the essential sheets.
Setting up the tracker
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). For example, enter different brokers and/or exchanges. It is possible to increase this limit. Contact me for more information.
- Step 2: Go to the History sheet. Take a look at how the data has been entered. Make sure to enter the slang of the currency instead of the symbol. For example do not use “BTC” but use “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. Do not change any data here.
- Step 3: Go to the positions sheet. Add the “=” sign in cell A3 so that the text turns into a formula. This custum formula perform calculation on your history sheet and produce three columns: symbol, shares and average cost for shares according to the FIFO method. The rest of the calculations are then performed based on these three columns. Do not change anything here, but instead change the input data in the History sheet.
- Step 4: Inside the cryptocurrency box, you will find a list of the cryptocurrencies you own in column “G’. Fill in the “Symbol/Currency” in column “F” similar to the example. The data in column “F” and “G” are required to create a sparkline in the dashboard.
- 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 6: Fill in the original currency in which you purchased the stock inside the designated box for stocks. Change only the data in column N!
- Step 7: Click on ‘activate trigger’. This will activate a trigger for the IMPORTHTML functions which retrieve the price of cryptocurrencies (15 mins) and sector/industry (12 hours). You will need to give authorisation in order for the script to run. This is standard procedure for running ANY script. Give authorisation by clicking continue > chose your gmail account > advanced > go to portfolio (unsafe).
- Step 8: Optional: Update price data of cryptocurrencies and sector/industry for stocks manually in the custom menu as required
- Price data for crypto is retrieved from Coingecko through the IMPORTHTML function. It refreshes every hour automatically. If you activate a trigger it will force a refresh every 10 minutes. ou MUST use the slang of the cryptocurrency as opposed to the symbol. For example, bitcoin instead of BTC.
- Check if you are using the correct slang by searching your coin at the coingecko website. Then, look at the last part of the URL. This has to be exactly the same as the slang you are using. For example, for THETA the link is: “https://www.coingecko.com/en/coins/theta-network“. Meaning the slang to be used in the history sheet is “theta-network”.
- Price data for stocks is retrieved with the Googlefinance function and refreshes every 20 minutes. Stocks and the local currency 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 total purchase costs in the history sheet must assume all costs including exchange fees and transactional costs.
- The money-weighted rate of return (MWRR) is used to calculate the performance of your total portfolio. Make sure to input all cash inflows & outflows correctly if you want this number to be reliable (e.g. all deposits & withdrawals from accounts).
- The “cryptofinance” function used to create a sparkline in the dashboard has a limit of 25/requests per day. If you don’t see a graph pull up, it is possible that you reached your limit.
- If the spreadsheet is too large or too small for your screen, go to “view” and change the “zoom”. For example, on a laptop that is 15 inch, you may prefer a zoom of 75%.
- The tracker supports by default 100 stocks and up to 30 cryptocurrencies. It is possible to extend this to as many as you like. Contact me for more information regarding this matter.
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 you will find your daily performance and the performance of your cryptocurrencies and stocks. Moreover, you can view your current cash balance and account balance on the top right. In the middle on the right you can see your total unrealized gains and the performance of your entire portfolio.
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 bitcoin) 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.
This is the data input sheet. As a user you are required to fill in this sheet with your data. The spreadsheet then makes use of the information inside this data sheet to perform all calculations. If you wish to change anything in the rest of the spreadsheet, then do so by making a change in the history sheet.
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.
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 insight in how well diversified your portfolio is by the industry and sector allocation. Finally, an overview of your balances per account is presented in the account balance graph. Hover over with your mouse to view specific values.
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 “H4” you can chose whether you wish to view your total portfolio, cryptocurrencies or stocks. 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 on your total return might be minimal. The final charts are gauge meters displaying your total return according to the money-weighted rate of return and the total amount of dividends received.
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”.