Investment Tracker Update

It is my pleasure to introduce the first update of the Investment Tracker, now referred to as Version 1.2. What’s new? In the previous version, there were numerous requests for web-scraping functions such as IMPORHTML and IMPORTXML, which considerably slowed down the performance of the spreadsheet. I successfully reduced the total number of functions from over 25 for 10 cryptocurrencies to a mere three for 30 cryptocurrencies. As for performance, the old spreadsheet took over 10 minutes to load 30 data points from cryptocurrencies, while the new one accomplishes this in under 10 seconds!

How did I achieve this?

My first approach was to minimize the total number of functions that run by default when opening the spreadsheet. I developed a compact script for the industry, sector, and newsfeed functions, and connected them to buttons labeled “Go” and “Reset.” By default, no function runs when the spreadsheet is opened. However, users can activate the scripts by pressing “Go,” which will enable the web-scraping functions and extract the requested data. Once users are finished, they can press “Reset” to clear the functions. Consequently, these functions will not run automatically when the spreadsheet is opened next time, significantly improving performance.

My second strategy involved linking three spreadsheets to each other. The cryptocurrency price necessitates two web-scraping functions: IMPORTHTML for the current price and IMPORTXML for the current day change. Tracking thirty cryptocurrencies would require one spreadsheet to load sixty of these functions, placing a substantial burden on the tracker. To address this, I divided the functions between two separate spreadsheets, each running thirty functions, equivalent to tracking fifteen cryptocurrencies. The actual tracker then imports data from these two spreadsheets using two IMPORTRANGE functions, reducing the total number of functions from sixty to two.

Setting up time-driven triggers

An optional configuration for the investment tracker is the implementation of time-driven triggers. By default, all web-scraping functions refresh every hour. If that suits your needs, there’s no need to continue reading. However, if you prefer checking your investments every ten minutes and want the tracker to be updated accordingly, setting up time-driven triggers is useful. To do this, run a script in the spreadsheet at least once to authorize it. I have created buttons for this purpose. After authorizing the script, navigate to the 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. If you’re having trouble, I’ve created a video that demonstrates how to link the spreadsheets and set up time-driven triggers.

Watch the video to learn more!

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

Moreover, benlcollines has written an insightful article on using google spreadsheet as a web-scraper.