2 Solutions to Fix the Most Recent Yahoo Stock Price Break in My FREE Google Stock Portfolio Tracker

The comments and my messages were flooded with questions regarding why the prices in my Stock Portfolio Tracker is not updating accordingly.

I created a Free Google Sheet that helps you track your portfolio based on transactions.

You can find out more information in this comprehensive article here. Free Online Investment Stock Portfolio Tracker Spreadsheet.

I am immensely proud of my spreadsheet because back then, you couldn’t really find any spreadsheet that tracked your portfolio based on transactions. Most spreadsheets track your portfolio based on one-time buys, which is unrealistic.

My spreadsheet may give you some ideas on how to design yours.

One of the nice features of the spreadsheet is that it auto-fetch the price of your stock holdings. By default, you can rely on Google Finance’s price quote, but Google Finance’s price quote is only limited to certain exchanges.

In my spreadsheet, I show how you can grab prices from Yahoo.

However, from time to time, the price fetch method breaks.

Like now.

Enough people told me that they see the following in their own stock tracker:

Wah, how come like that?

Okay, I have two fixes for you. I find both pretty easy to implement.

However, let us talk about the problem.

Why did the Yahoo Stock Quote Function break?

Honestly, I have no idea.

Likely, large sites that provide these data regularly prevent people from scalping their website for prices. They would rather you pay for it.

In the most latest iteration, we make use of functions getYPrice2, or getYPrice3 to go to Yahoo and grab the prices for the respective stocks

If you go to the Extensions Menu, then Apps Script, you will be able to find the following functions:

These functions return “-1” because they delivered a responseCode that is not 200. Basically, the functions got nothing from Yahoo.

Basically, they don’t work.

Now… at this point, you would say: “Kyith, can you just cut the BS and tell me how to fix this thing?”

Solution 1: Use getYPrice 2 and Change the Yahoo Query URL from V7 to V6

This one will only work with my function getYPrice2 and not getYPrice3.

Go to the Extensions Menu, then Apps Script, then find getYPrice2:

Change that v7 to v6.

Then, refresh your Google Sheet. It should work.

You should see this:

Simple right?

Solution 2: Pull from Financial Times using ImportXML

Now, I am not sure how the former solution is going to continue to work, but having one more alternative is always good.

We can attempt to pull data from the Financial Times. Financial Times have stock and fund quotes for a lot of different exchanges around the world.

The downside of my solution is that ImportXML can be slower in the update, but if you just want a hassle-free way of updating and don’t need the most up-to-date data, then this is good. Financial Times can also change the formatting of their website and that would break your price fetches.

You can see that in my actual portfolio, I make use of the following formula to fetch the data from the Financial Times:

You can just put the following formula in column F2 and then drag from cell F2 downwards so that all the rows will have the same formula:

=if(not(isblank(E2)), split(IMPORTXML("https://markets.ft.com/data/etfs/tearsheet/summary?s="&E2,"//li[1]/span[2]")," "),"")

This formula basically checks that if cell E2 is not blank, and has a FT Quote, then we will use ImportXML to grab from https://markets.ft.com/data/etfs/tearsheet/summary? using the quote you put in column E2.

For example, for SPDR MSCI USA Small Cap Value Weighted UCITS ETF, which is listed on the London Stock Exchange the quote is USSC:LSE:USD.

So the string will end up as https://markets.ft.com/data/etfs/tearsheet/summary?s=USSC:LSE:USD.

How do I know the quote is USSC:LSE:USD?

If you go to FT, you can search either the fund name or the quote USSC:

But why do I use a Split in the formula?

“//li[1]/span[2]” will pick out the entries in the website that match the first [li] and the second [span].

There are situations where we will have two entries:

1. The price quote
2. Other stuff

Split and ” ” allows us to select only the first stuff, which is what we want.

The great thing using Financial Times is that you can also update the prices for some of your unit trusts:

In the screenshot above, I managed to successfully pull the data from the Dimensional World Allocation 60/40 SGD Accumulating, the Dimensional World Equity SGD Accumulating and the Dimensional Global Targeted Value SGD Accumulating. All funds are prevalently use by local investors.

I tried this with:

1. Singapore stocks
2. Singapore unit trusts
3. London Listed ETFs and stocks
4. US stocks and ETFS

I hope that you are fine with both solutions. If you are not, I cannot help much.

Update 2024 Mar 20th: Readers have let me know that ImportXML does not work so well. So I have coded a App Script version to retrieve prices which tries its best to circumvent Google. You can find the implementation here.

A Better Way to Grab Stock, Fund, and ETF prices from Financial Times >>

Do share this with more people if you think it will help them.

---

I invested in a diversified portfolio of exchange-traded funds (ETF) and stocks listed in the US, Hong Kong and London.

My preferred broker to trade and custodize my investments is Interactive Brokers. Interactive Brokers allow you to trade in the US, UK, Europe, Singapore, Hong Kong and many other markets. Options as well. There are no minimum monthly charges, very low forex fees for currency exchange, very low commissions for various markets.

To find out more visit Interactive Brokers today.

Join the Investment Moats Telegram channel here. I will share the materials, research, investment data, deals that I come across that enable me to run Investment Moats.

Do Like Me on Facebook. I share some tidbits that are not on the blog post there often. You can also choose to subscribe to my content via the email below.

I break down my resources according to these topics:

1. Building Your Wealth Foundation – If you know and apply these simple financial concepts, your long term wealth should be pretty well managed. Find out what they are
2. Active Investing – For 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
7. Providend – Where I used to work doing research. Fee-Only Advisory. No Commissions. Financial Independence Advisers and Retirement Specialists. No charge for the first meeting to understand how it works
8. Havend – Where I currently work. We wish to deliver commission-based insurance advice in a better way.