GOOGLE FINANCE function returns no quote data

[cpm...@gmail.com]

Hello,

I have been using Google Sheets and the GOOGLEFINACE function for years.

Starting on 2/5/19, it has not been able to get quotes on some ticker symbol. It seems to be sporadic and mostly on Closed End Funds.

It has also returned incorrect quotes.

Has anyone seen this ? Something seems to be wrong with the quote feed.

I get this message:

Error When evaluating GOOGLE FINANCE, the query for the symbol: 'UTF' returned no data.

Thanks

[David]

I am having the same issues. I am using =GoogleFinance(A9,"price") where A9 correlates with a particular stock price, in this case, EXG. Last week it pulled the correct price. Now it is pulling back a price of $0.02 for something that is around $17. This is happening with a total of four of my stocks/funds: PCQ, CLM, NAC, EXG.

[cpm...@gmail.com]

Yes - it sounds like the same problem I am having. It still is not fixed. Regular Stock and ETF quotes work fine. I am just having problems with Closed End Funds, which are a kind of mutual funds.

I tried using this function that was posted by another person with similar issues, but it does not work for all my Funds. It worked on some of them..(A2 points to Cell holding symbol to get quote on).

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",A2,"&insttype=Fund"), "table"),3,1)),7))

[Matt P]

A better way is get a quote from iex and use the importdata function

But Google, please fix this!!!!

[Robert Lewis]

Same issue here - still not fixed today. Some but not all CEFs affected. I also noticed that the function =GOOGLEFINANCE($B40,"name") where $B40 is the cell containing the fund symbol, returns either #N/A or a completely wrong symbol name. cpm's link to bigcharts below works for me. I found another work-around someone else posted that sort of works also. It seems to not update after hours and reports $0 in the AM until the market opens. 

Anyway, here is that example:  =DGET(IMPORTDATA("https://api.iextrading.com/1.0/tops?symbols=NDP&format=csv"),"lastSalePrice",{"symbol";"NDP"}) where NDP is the fund name.

This same thing happened a year or two ago and lasted for several months, if I remember correctly. At that time I tried using the JSON API which worked but was a little more complicated to get going. So that might be another option for some. Thanks to all for the comments and knowing I'm not alone with this issue. Hoping Google gets their act together sooner this time.

R

[David Southerland]

Yes, I have the same problem.  CEFs no longer appear on GoogleFinance.  This has happened several times over the last year, but was always fixed within a few days.  This time, however, it has been more than a month and it is still broken.

The workaround solution from bigcharts is just what I needed.  Thanks to the author of that one.  It is not working for prices less than $10 because it includes the quote from the previous part of the string, but if you change the "7" at the end of the formula to "6", it will return the lower prices correctly.

[hall...@gmail.com]

Same issue here. ARCC a BDC and THW a CEF are producing the error message, while PDI, FFC, UTG and JPS all CEF's return incorrect quotes varying from $.01 to $883.50 yesterday. Is there a workaround to point Google Sheets to other services other than googlefinance?

[David Atherton]

I get this all the time, even minute to minute. It is clear that Google have problems with their data feed and basically don't really care. The S&P changes from being .SPX to .INX (who calls it that). GOLD seems to be 1/100 of XAUUSD, but then it isn't. There is no DXY, and OIL's price is dodgy.

Note btw that getting =GOOGLEFINANCE(symbol,"price",date) if date is today doesn't work, you have to omit the parameter. This is often the case even after the market closes (as "price" when market is closed is effectively "close")

There are several free Chrome add-ins which provide more reliable prices. tbh, unless you are doing a large cap share-only (not ETF not funds) portfolio, I would use them. Two tips

1. Add the full GF name ie NASDAQ:AAPL rather than just AAPL

2. (Not related to symbols), use =QUERY(GOOGLEFINANCE( ....) rather than =GOOGLEFINANCE raw, to get round these little tables that =GF produces.