Simple guide to the Basic Crypto&Stocks Investment tracker

General information

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 choose another guide from the menu.

Welcome to your newly found unique investment tracker. The tracker works for cryptocurrencies and stocks alike! Not only will it track your investments portfolio for you, but 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. This version offers most investors all the tools they need! However, if you are searching for more features such as net worth tracking and an overview of your realized gains (FIFO/LIFO), check out the premium trackers.

This page consists of two parts. The first part describes 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

Additional information

• 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. You 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

Dashboard

The first sheet you will encounter after the Setup sheet is the Dashboard. Here, you will find a summary of the 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, click on the arrow next to the symbol’s name (in this case, bitcoin), and the tracker will present you with some financial metrics and a sparkline! The waterfall graph illustrates 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 the impact on your portfolio may still be small.

History

This is the data input sheet. As a user, you must fill in this sheet with your data. The spreadsheet then uses the information inside this datasheet to perform all calculations. If you wish to change anything in the rest of the spreadsheet, then do so by changing the history sheet.

Positions

Arguably the most crucial sheet is located in the “Positions” tab. This sheet provides you with an overview of all your current holdings. It includes vital 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 into how well diversified your portfolio is by the industry and sector allocation. Finally, the account balance graph presents an overview of your balances per account. Hover over with your mouse to view specific values.

Investor return

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 choose 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 your portfolio return. This is especially useful since one of your holdings may have lost 90% of its value; however, if you only spend $100 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.

Newsfeed

In the Newsfeed sheet, you can go to cell “D7” and click on the arrow, and the tracker will present you with a drop-down list of all your stock holdings. Chose one stock holding and then click on the source of your choosing. For stocks, you can select from “Seeking Alpha” or from “Yahoo Finance.” If you are not interested in news from stocks, you can also choose to look up general news on cryptocurrency from “Cryptoninjas,” “Coindesk,” or “Cointelegraph.”

Financial Screener

The following tool available for your use is the “Financial Summary.” This sheet extracts a table from finviz.com. The table provides you with financial information on a specific stock. Enter in cell “C3,” the symbol of the stock of your interest. The table should update automatically. Unfortunately, Finviz only supports US stocks. A graph displaying the historical price is also present. You can change the chart by entering a different date in cell “P4” up and till cell “P10”.