Client

The client worked in the investment space, constantly evaluating pitches for companies across a variety of sectors.

They monitored stock price data and other key financial metrics, such as analyst estimates and quarterly results via a Google Sheet, often on-the-go.

The Problem

While Google Sheets has a built-in function to look up data directly from Google Finance, many companies are not covered on there, especially in foreign markets such as the Asia-Pacific region.

Instead, we had to look on an alternative website, and find a way to pull that data into the spreadsheet.

Design and Development

My first instinct was to use something like Google Sheets’ =IMPORTXML function to simply load the URL for any given company, and get the relevant portions via CSS selectors. Within 15 minutes, I thought I was done.

This worked well for one company, but Google Sheets failed once I tried this for several URLs. A bunch of the cells would get stuck on “Loading” indefinitely. Maybe we could wrap multiple companies up into one function?

I was able to do that with Google Apps Script. However, Google can (and will) throttle requests running from scripts, so this was not a reliable solution either.

Instead, I spun up Puppeteer on a cheap VPS and visited the pages from there.

While I could have used vanilla cURL, I wanted to keep my options open for manipulating the browser if needed. For example, we might conceivably have to use the company search bar, etc.

The program needed a persistent “watchlist” of sorts – a canonical list of companies (URLs) to scrape on a regular basis. The user could add companies via a big button on the Google Sheet. They could also manually call for a re-scrape, in case they needed an update immediately (vs. nightly).

The final output: one master CSV containing the latest scraped data.

Results

Every night, these sometimes-obscure stocks get scraped and written to one big CSV, which then gets picked up by the Google Sheet via =IMPORTDATA. The rest of the work – lookup formulas and adjustments for currency or order-of-magnitude – was straightforward.

The client was happy, and essentially got a makeshift API out of this website that provided a fast, portable, and accurate look at several key sectors.

I actually used your tool in a pinch during a negotiation yesterday.

On the spot I was able to pull up your file and compare operating and trading metrics [to prove that this country’s stock market] wouldn’t be the savior he thought it was.

I think before [I would have needed] to source some dated equity research for a rough approximation.