The users of my FREE Stock Portfolio Tracker, which tracks stocks that you own by transactions are complaining that their prices were not updating.
Specifically, the Yahoo prices on Yahoo Data Ref are not updating.
And they were wondering if it’s my spreadsheet acting up again.
The answer is no.
If you view the Yahoo Data Refs, you will see something like this.
What happens is that Yahoo, which got bought over by Verizon, decided to shut down the Yahoo Finance API, which they deem we are using not legally.
It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com
This affects not just my Stock Portfolio Tracker.
Many folks get their live prices and data through this method for their website, Microsoft Excel and other forms of software.
You are likely not the only one disappointed.
I am too. I use my stock portfolio tracker as my main way of tracking.
This comes shortly after Google has decided to take down their stock portfolio feature.
So this has not been a good week.
How does this Impact You?
In the past, we have some outages.
Some of these are due to how Google Spreadsheet handles the data call.
Some of it is due to Kyith’s incompetency in implementation.
Some of it is due to the Yahoo Data.
This time it is likely this Yahoo Data Source is dead.
So this means that you need to rely on other data sources.
The Stock Portfolio Tracker takes its prices from Google or Yahoo.
The natural fallback plan is to use Google.
If your portfolio contains USA and Hong Kong stocks, Google still has prices for those. Your impact will be less.
But if your portfolio has exchanges that are not supported by Google (for example Singapore), you will face a problem.
You will need to find alternative data sources.
If you have those, you might be able to integrate them into the stock portfolio tracker.
Manually Update your Financial Asset Prices
One solution is to manually update the prices.
There is a reason I came up with a manual price column. It is meant for a situation like this.
If Google and Yahoo Stock Prices are not working, the manual price will take over.
Getting Data From Yahoo Finance Data via JSON Retrieve
As of Dec 2020, I was able to successfully retrieve the latest prices through function calls to a particular Yahoo Finance page.
I created a function in Tools > Script Editor to try and get prices from a query string in Yahoo Finance. The query string from Yahoo Finance provided the result in JSON format.
This works now and you can access this solution by reading this article Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet
The article shows how existing Stock Portfolio Tracker users with older versions can port and use this new implementation.
A lot of Scraping Solutions Failed Now
A reader of mine who commented below gave us a really nifty trick.
You see, the problem with scraping solutions is that, if the website you are scraping from changes things, it will break.
Secondly, if you have a lot of stocks, the generation would fail.
This is where the reader’s suggestion comes in. We basically use the IFERROR to catch that if Google Sheet fails to pick the price out from one source, we automatically try to grab from another source.
I currently import data from WSJ and Bloomberg for Singpore. I use an Iferror function to pick the first one that loads a value.
With the ticker in G8 of course
What I observe is that many of these scraping solutions failed over time.
Is the Stock Portfolio Tracker inferior now?
I think we lost something vital but for me, the beauty of the Stock Portfolio Tracker has always been its ability to track what you own by buy, sell, dividend, stock split transactions.
Whether price updates automatically is good to have.
For some users, Google prices currently are there, and for Singapore users, with this workaround, the only tired person is Kyith.
Unfortunately, if your exchange is not supported by Google, or not in Singapore you have to find the way to get the prices, and they might eventually still integrate well in Stock Portfolio Tracker.
Did you guys find some other data sources?
What other alternatives are out there for you?
Some of the write-ups on the subject:
- Free Online Investment Stock Portfolio Tracker Spreadsheet
- Updates to the Free Google Stock Portfolio Tracker
- Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet
- Automatically get Yahoo Finance End of Day Stock Price in your Google Spreadsheet
- New 6-Month Singapore T-Bill Yield in Late-Late-March 2023 Should Drop to 3.7% (for the Singaporean Savers) - March 23, 2023
- We Cannot Always Be Shifting the Goal Posts When Doing Retirement Food Planning - March 21, 2023
- CPF’s New Personal Retirement Income Planner Tells Me I Secured a $1,430 to $1,770 Real Monthly Income to Successfully Cover My Most Important Retirement Lifestyle - March 18, 2023
Sunday 23rd of January 2022
Thanks for the awesome portfolio tracker. It has helped me so much in comprehensively keeping track of my positions these years. Recently, I noticed that Google Finance is no longer able to retrieve the stock price of one stock - iShares Hang Seng TECH ETF (3067.HK). I was wondering if you may have some advice on what I could do about this?
Friday 28th of January 2022
hi yw, unfortunately for some reason Google doesn't work for ETF in hk. So you might need to rely on the Yahoo option.
Mikhail from Russia
Friday 3rd of September 2021
Copied, prices work fine (if US then GoogleF shows, if not - Yahoo), opened script editor, approved permissions, ran - fine also! Thanks, Kyith! Very handy and reliable spreadsheet! Main thing - it still works fine almost for 10+ years despite some permission issues etc. it's a masterpiece for sure and no similar sheets around available! Thanks for all your efforts and support!
Monday 6th of September 2021
Hi Mikhail, thanks for the shout out. Glad that you are a long time user!
Thursday 3rd of June 2021
Hi Kyith, amazing and helpful tracker, appreciate you for sharing this with everyone.
Wanted to ask, for about a month now my "Portfolio History" tab populates daily row but it's identical to May 12 2021 row. Meaning no change every night, but we should expect nightly fluctuations. How do I resolve this? The "Portfolio Summary" tab has daily fluctuations as expected.
Thanks for the help!
Friday 4th of June 2021
Hi Kyith, I just checked my trigger google script again, no issue though. So I don't think that's the problem.
Friday 4th of June 2021
Hi Kyith, ok will take a look at that. The strange thing is that the script has worked fine for the past year. Only in the past month, the issue surfaced.
Friday 4th of June 2021
Hi Jacob, I think you would have to set the Trigger in the Google Script such that on X time of the day, the Script will move the data from Portfolio Summary over to Portfolio History. In my main article, I have written how to set the trigger: https://investmentmoats.com/stock-market-commentary/portfolio-management/introducing-our-free-stock-portfolio-tracker-spreadsheet/
Tuesday 5th of May 2020
Your portfolio tracker is amazing. Have been using it since my investing journey.
However I have some problems with updates of the stock prices. I understand that now we are fetching with your own data that you update yourself, can i just check how often you update it? It seems to me that for the past week, the prices haven't been updated.
Or is there a better alternative to this new getSGPrice() method now?
Saturday 30th of November 2019
Thank you! I just can't say how much I appreciate your work. I've just started investing and your solution is the best
Saturday 30th of November 2019
Hi Anton thanks for the feedback. Means a lot to me.