Parsing stock quotes in Microsoft Excel and Google Sheets from any website

It happens that private investors do not trust services for maintaining a securities portfolio and keep track of their investments in Excel or Google Sheets.

If the number of securities is not so large, then such use of tables is justified:

no need to pay anyone to store data;
no one will delete your file, for example, for inactivity;
reports can be made the way you like.

But this accounting method also has its disadvantages, mainly related to the need to manually update quotes. If you do this once a quarter, it’s easy to do manually, but to keep it up to date more often, it will take a lot of time: you need to go to the website where the current quotes are published, find the desired price, copy it and paste it into a table cell. And so on for each security in the portfolio. Sad and long.

Why do we need current prices in tables at all:

Investors – for effective portfolio and risk management.
For traders – to optimize buying and selling decisions with maximum profitability.
Analysts – to better understand market trends and improve the accuracy of your forecasts and reports.

In this article I will tell you how to automate the process of downloading quotes. Below you will find ready-made files with examples of obtaining prices, which can be copied and pasted into your table with minor changes.

Problems getting quotes into any tables

Although the ability to automatically obtain quotes in Excel or Google Sheets simplifies portfolio management, there are several technical obstacles that can be encountered when parsing or scraping (in general this is the automatic collection of data from the Internet, in spreadsheets it works through formulas or scripts) :

Dynamic content loading: Modern websites often use JavaScript to load current prices after the page has initially loaded. This creates a problem for basic parsing methods.
API restrictions: Some websites and financial institutions offer public APIs (for example, the Moscow Exchange or the Bank of Russia), but these also have their limitations.
And it happens that you can find an API, for example for investing.combut to take advantage of it will require the search for alternative methods – simulating a person to obtain data – using browser automation.

Download quotes in Microsoft Excel

Until recently, automatic loading of quotes for some foreign stocks was available directly in Excel – with a Microsoft 365 subscription, but since March 2024 this has not worked.

An easily scalable way is VBA, which is the internal programming language of Microsoft Office. With its help, you will have to write a macro, a mini-program that performs several actions at once. I have already written the code – it will be enough to replace a couple of lines in it to suit your needs and insert it into the table.

While tools like Power Query can also achieve similar results, VBA provides greater flexibility and scalability.

To retrieve Russian quotes, you can use the API provided by the Moscow Exchange. By integrating this API with Excel, you can pull prices for bonds or other asset classes with a 15-minute delay directly into your spreadsheets without manual updates:

Prices via API provided by the Moscow Exchange

For popular foreign securities I wrote VBA:

To receive courses from the Central Bank of the Russian Federation, I also had to write a VBA script, because the formula recently stopped working:

⚠️Example file for downloading quotes in Excel: Quotes for any security in Excel (download the file to your computer, otherwise it will open in Google Sheets and there will be only errors).

Download quotes to Google Sheets

As for Google Sheets, the built-in function is suitable for many foreign assets =GoogleFinance()which allows you to extract historical and current prices for various foreign assets. However, the limitation is the complete lack of data on Russian stocks and bonds, which is a critical gap:

For Russian securities:

Bank of Russia exchange rates:

Obtaining, unlike Excel, through a formula

⚠️Example file for downloading quotes in Google Sheets: Quotes in Google Sheets

If you are an advanced user, you can find the API for any popular site, for example investing.com, and using browser automation, for example, the Puppeteer library in Node.js, you can bypass Cloudflare protection and automate the extraction of quotes. This method requires more advanced understanding, but it opens up opportunities to access protected data that simple scraping can no longer handle.

Key Findings

Automated quote integration – be it international or Russian stocks, automating quote retrieval saves time and reduces errors in financial analysis.

To overcome the challenges of web scraping: Dynamic web content and bot protection can be overcome using APIs or browser automation tools, making them essential for serious investors.

Excel vs Google Sheets: While both platforms offer solutions for extracting market data, they each have their own strengths. VBA in Excel provides powerful customization capabilities, while the simplicity of Google Sheets is enhanced by cloud connectivity.

Practical data collection techniques can help both new and experienced users better navigate financial markets, stay informed, and make timely decisions. Whether you're building a portfolio, executing trades, or conducting in-depth market analysis, there's a scraping solution to suit your needs.

Author: Mikhail Shardin

October 21, 2024