Automatically get Yahoo Finance End of Day Stock Price in your Google Spreadsheet | Investment Moats Skip to Content

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.

Auto update of Stock Prices from Yahoo Finance

In my stock portfolio tracker, you will observe that the Auto Price column shows daily end of day Singapore stock share price. The price data is retrieved from a server end of the day but you do not have to use Manual Price to update your prices often.

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)

How to Scrape Yahoo Finance Stock Prices using Google Spreadsheet

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

How to scrap Yahoo Finance Stock Prices

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

If a Singaporean eats out Everyday for A Year, He could spend $17,000. Zhun Bo!
← Previous
11 Deeper Things I Learned about Manulife US REIT
Next →

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Lulu

Monday 8th of March 2021

Hi Kyith, notice that the spreadsheet is no longer able to pick up the stock price for SGX from Yahoo. Wonder is it my issue or that yahoo no longer provide such service? Thanks

Lulu

Monday 8th of March 2021

@Kyith, I am still using the attachment in this post. I just realise that you have a post on 20 Dec last year on this, I will take a look. Lets see whether I understand. I am not some one who can code very well. Thanks

Kyith

Monday 8th of March 2021

hi Lulu, there have been some changes along the way. now the working version is this function called getYPrice2 or getYPrice3. Are you still using that?

Kanna

Wednesday 29th of July 2020

I had issue with getting Yahoo prices, I made a function to get Yahoo price using resources from internet https://gist.github.com/kannaiah/53881607c9eba63099689591ad6e949e#file-getyahooprice-gs Could be useful to others. Thank you

Kyith

Wednesday 29th of July 2020

Hi Kanna, thanks for your attempt with this. Can I find out how many stock quotes have you tried this with? Many Thanks.

Valentin

Saturday 11th of July 2020

The Formula works fine !!!, Thank You very much. I am not a grabber-expert. Any idea if it is possible to grab the adjusted close data at a specified historical date?.

Regars, Valentin

S

Thursday 18th of June 2020

I love this! I've been looking for an article that would tell me how to pull a specific number from Yahoo Finance and this is so great.

Do you have any advice on how you would pull a specific number on a more detailed page, like Statistics? Trying to see if I could feed in a specific number from the Income Statement into a Google Sheet.

Thank you!

Kyith

Friday 19th of June 2020

HI S, I think you got to be comfortable with HTML tags so that you can make use of the importXml to get value from Yahoo Finance or other sources. If the page data is static you can traverse by going to a specific HTML tag like div id or class name and zoom into the figure you want.

Mr Llama

Monday 1st of June 2020

Great sharing!

This site uses Akismet to reduce spam. Learn how your comment data is processed.