Automatically get Yahoo Finance End of Day Stock Price in your Google Spreadsheet

Everyone have their own way of tracking their own wealth portfolio.

I have my way as well.

Some years ago I created this Google Spreadsheet called Stock Portfolio Tracker which allows you to track your own stock portfolio by transactions.  I put my own portfolio out here as a demo of its capabilities.

Did I tell you the Google Spreadsheet is FREE with no strings attached?

You can get the spreadsheet through that link above.

One of the beauty of the Stock Portfolio Tracker is that it allows you to update the stock price automatically.

This means that you do not have to spend time filling in the latest asset prices.

In my original stock portfolio tracker, I gave the option of pulling the stock data from Google Finance and Yahoo Finance. Google Finance is very limited in the number of exchange supported, so for most international investors who would like to make use of the spreadsheet, we will prefer Yahoo Finance.

However, since Nov 2017, Yahoo Finance have killed the API that allows us to query the stock prices.

In this article, I provided a work around by allowing you to link to the end of day Singapore Stock Prices on my server. In this way, after 7 pm, you would be able to get Singapore prices.

The gold is in the comments of that post above, because many readers have given various suggestions of different ways they manage to get stock data from Vantage, Reuters, Bloomberg website.

One of the solution is pretty good.

It essentially leverages on the scraping of web page data to get the latest prices. Scraping a web page for information is not new.

However, it is not my preference to recommend scraping because if the developers change the layout of the Yahoo Finance website, the code will not work anymore.

Here is the Workable Scraping Solution (for now)

In the illustration above, you can see a few different stock name, their corresponding stock quote and prices being fetch.

I have tested them on a few different stock exchange including:

1. Singapore
2. Australia
3. Hong Kong
4. Malaysia
5. London
6. Netherlands
7. Germany

Here is the Google Spreadsheet that I used.

With this, you can learn from how I do it, and adopt for your own Google Spreadsheet.

To make a copy of the above spreadsheet, go to File then Make a copy…. and you can use it.

1) You will need to find out the stock symbol / quote of your stock in Yahoo Finance.

So go to Yahoo Finance, type in your stock name and find the stock quote

2) Next, grab it with the following formula

=ImportXML("https://sg.finance.yahoo.com/quote/"&B2&"/history?p="&B2, "//tbody/tr[1]/td[6]")

Where B2 is the cell you put in the Stock Quote in #1. There are 2 places of B2 you need to change to your cell.

3) You will see the stock quote grab successfully

The screen shot above illustrates to you how the scraping works.

You are essentially trying to grab the end of day adjusted close data. So do not expect your prices to update real time or delayed!

The importXML allows you to grab the first tbody tag, then the first tr tag, and the sixth td tag.

If the formatting of this web page change or this url changes, you cannot grab the prices any more.

Summary

I am not supporting this, and this might give you guys some ideas how to scrape from other sources.

From my observation, this even work well for a page where i have like 70 stock quotes. Not all scraping solutions work for so much stock quote from what I noticed.

I gotta give a shout out to one of my readers for letting me know this.

This is cool stuff.

If you would like to read the series of articles on this, here it is in order:

1. Free Online Investment Stock Portfolio Tracker Spreadsheet
2. Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet
3. Yahoo Finance Data Shuts Down – My Modification to My Stock Portfolio Tracker

If you have other solutions that is not found in article #3, do share it with us here!

Do Like Me on Facebook. I share some tidbits that is not on the blog post there often.

Here are My Topical Resources on:

1. Building Your Wealth Foundation – You know this baseline, your long term wealth should be pretty well managed
2. Active Investing – For the active stock investors. My deeper thoughts from my stock investing experience
3. Learning about REITs – My Free “Course” on REIT Investing for Beginners and Seasoned Investors
4. Dividend Stock Tracker – Track all the common 4-10% yielding dividend stocks in SG
5. Free Stock Portfolio Tracking Google Sheets that many love
6. Retirement Planning, Financial Independence and Spending down money – My deep dive into how much you need to achieve these, and the different ways you can be financially free