The first update for the investment tracker

It is my honor to share with you the first update of the investment tracker – from this moment on named verson 1.2. So what is new? In the old investment tracker, there were many different requests for web-scraping functions such importhtml and importxml. This significantly slowed down the performance of the spreadsheet. I managed to reduce the total functions from >25 for 10 cryptocurrencies to only three for 30 cryptocurrencies. What does this mean in terms for performance? The old spreadsheet would take over 10 minutes to load 30 data from cryptocurrencies, while the new one can do this in less than 10 seconds!

How did I pull this off?

My first action was to reduce the total amount of functions which are run by default when opening the spreadsheet. I created a small script for the industry, sector and newsfeed functions and linked those with buttens: “Go” & “Reset”. By default, no function is ran when the spreadsheet is opened. However, if the user wishes, he or she can activate the scripts by pressing “Go” which will activate the web-scraping functions and extract the requested data. When the user is finished, the “Reset” butten can be pressed in order for the functions to be wiped. When the spreadsheet is opened next time, these functions will not run automatically, significantly improving performance.

The second action was to combine three spreadsheet by linking them to each other. The price of cryptocurrency requires two web-scraping functions: IMPORTHTML for the current price and IMPORTXML for the current day change. For tracking thirty cryptocurrencies, this would result in one spreadsheet loading sixty of these functions which puts a significant load on the tracker. Therefore, I split the amount of functions ran in two seperate spreadsheets where one spreadsheet is running thirty functions equivalent to tracking fifteen cryptocurrencies. In turn, data from these two spreadsheets is imported by a two IMPORTRANGE functions in the actual tracker, reducing the total amount of functions from sixty to two.

Setting up time-driven triggers

An optional set-up for the insvestment tracker is the set up of time-driver triggers. By default, all the web-scraping functions refresh every hour. If this is to your satisfaction, then there is no need to read the rest of the paragraph. However, if you are one of those people that likes to check his investments every ten minutes then it’s nice to see the tracker actually updated. There are different ways to set up time driven triggers of scripts. The easiest would be to run a script in the spreadsheet at least once, in order to authorise the script. I have created buttens for you which you can click. After you have authorised the script, you should head over to script editor via the “extra” tab in the menu. Then go to triggers > add trigger > function cell refresh > head > based on time > minute timer > every 10 minutes > send me notification of failure every week. Don’t worry if you aren’t following, I made a video which shows how to link the spreadsheets and set up time-driven triggers.

Play the video to learn more!

Updated manual

This manual is an update to the previous manual which applies to the first version of the investment tracker. I will keep both versions live since not everyone needs to track thirty cryptocurrencies and the first version is easier to set up than this version.

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: Fill in three categories of your choice. Do not change the last category and leave it as “Deposit”. Examples of categories: cryptocurrencies, short-term investment and long-term investment.
• Step 3: 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. Also delete column “G” and “H”. Now fill in the history sheet with your transaction history. Your broker often provides this data. Add any fee’s to the total purchase cost. If necessary, you can use the sort option at the top to sort the data based on date or any other column of your choice.
• Step 4: Go back to the Setup sheet. Fill in the “Cryptocurrency” box as listed in the example. This box is necessary for the cryptofinance function, which is used to create a sparkline on your dashboard. The function has a limit of 25 requests/day. If you do not see any sparkline show up on your dashboard, it’s because you have reached your limit.
• Step 5: Fill in the currency you wish as output for your portfolio in the “Main currency” box. For example, chose “USD” if from the United States or “EUR” if from Europe.
• Step 6: Fill in the original currency in which you purchased the stock in the respective box.
• Step 7: Go to the ‘Live data cryptocurrency’ sheet. Follow the instructions on this page.
  • 1. Go to the first spreadsheet which extract live data from the first ’15’ cryptocurrencies: https://docs.google.com/spreadsheets/d/1aD4Kvm0dkX5laMLGJkbJ4xMRh5ZFxCGhar4zcb-2WF8/edit?usp=sharing
  • 2. Go to FILE > Make a Copy
  • 3. Copy the link of this spreadsheet and input the link in cell C4 of your new spreadsheet to import the names of your cryptocurrencies. Press “Allow” to provide access.
  • 4. Copy the URL link of your new spreadsheet and replace the link with the one in cell C3.
  • 5. Repeat this same process if necessary for cryptocurrency 15-30: https://docs.google.com/spreadsheets/d/1f0MgG-VYE9Luz0iwGK4WhvSzgnxVsafNP4AALLDPzh0/edit?usp=sharing but this time replace the new link in cell C19.
  • 6. Optional: Follow the instructions described earlier in this page to set-up time-driven triggers.

Further reading

If you wish to learn more about import functions, a good place to start is using the documents provided by google. Here are the functions used in this spreadsheet:

IMPORTHTML – import data from a given table

IMPORTXML – import data based on an x-path

IMPORTRANGE – import data from another spreadsheet

Similarly, benlcollines has an awesome article about how to use google spreadsheet as a web-scraper.