Challenge
The client worked in the investment and M&A space, constantly evaluating pitches for companies across a variety of sectors in the Asia-Pacific region.
They needed a cost-effective, reliable way to track key financial metrics on a predefined universe of companies.
To make matters worse, most of those companies’ data were not available on Google Finance or Yahoo Finance.
Solution
Once we identified a lesser-known website that provided data on almost all public companies on Asian stock exchanges, I worked on scraping it.
At first, I tried Google Sheets’ =IMPORTXML function, but running this function en masse (either directly in a formula, or using a Google Apps Script) resulted in Google aggressively throttling these requests.
Instead, I deployed a scraper and simple backend on a cheap VPS.
This backend was responsible for:
- maintaining a persistent “watchlist”
- scraping the data for all companies
- compiling the scraped data into a .csv file
The watchlist management was done via large buttons on the Google Sheet. Finally, the output .csv file was pulled into the Google Sheet all at once.
From there, a few formulas were required to clean up currency conversions and order-of-magnitude variations.
Outcome
This fast and portable application could run on any server, or even on a local computer if needed.
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.