Yahoo Finance api change?

[sedgefield02]

I use the yahoo financial api to download mutual fund prices each day. It has worked fine for months. Today I got

run time error 1004 application defined or object defined error

the apparent cause of the error is highlighted in bold below.

{ code
Set DataSheet = ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("BQ5"))
.BackgroundQuery = True
' .TablesOnlyFromHTML = False
.FillAdjacentFormulas = False
.Refresh BackgroundQuery:=False '<---- cause of the bug 11/2/2017
.SaveData = True
End With

end code}

I know Yahoo will make changes without any notification. Does anyone have information on this?

 

[FormR]

but it appears to pull historical data and not the 15 minute delayed current price that I'm looking for.

Hi, no - I used that article to get the 15 min delayed quotes - the google sheets formula for the current price is:
=GOOGLEFINANCE("Symbol","Price")

"Symbol" needs replacing with a share symbol - like "LON:BARC" for example.

 

[brynrogersTHG]

Hi...

The =GOOGLEFINANCE command does not work with standalone Excel. I have a rather complicated spreadsheet I would rather not take the time to convert to GoogleSheets if possible.
I also have the old Yahoo ticker code in a .NET app I developed for personal use, these are the reasons for not using GoogleSheets.

Thanks, Bryn.

 

[FormR]

The =GOOGLEFINANCE command does not work with standalone Excel.

Although it's a bit of faff once you have the google sheets file set up and published to the web as a csv it's a piece of cake to then import that data into Excel as a table and away you go using Excel (almost) exclusively.

Good luck with your search for alternatives though, post back if you find something - I'm sure all here would be interested.

 

[FormR]

I would rather not take the time to convert to GoogleSheets if possible.

The point is, you continue to use your Excel file - the google sheets part is simply a method that, once set up, allows you to import the share prices into Excel.

 

[merlin777]

I used the googlefinance() method via an imported googesheet into excel and it's a much simpler and more flexible process than the yahoo API and the VBA that's required.

The only thing I can't get back from this function is the exchange that the shares are on for a given ticker symbol.

 

[Stiuart_W]

I have an Excel workbook on my Mac where I keep track of all my finances including stocks and shares. Obviously I was impacted by this Yahoo API switch off.

I have gone down the route of using a Goggle sheet and the Googlefinance function set to publish. When published the link is copied into a Word document and saved as a plain text file (MS-DOS) with file extension .iqy.
In Excel of the Mac this is then pulled in using Data→Get External Data→Run Saved Query. I do find that it pulls the entire data in but Excel for Mac is putting each row from the Google sheets into a single cell so I have to split the price out (currently I'm only looking at price but in the future if I want to look at other indices I'll need to decide how to handle that imported data. I then have some code in the Open Workbook to refresh the querytable every 15 seconds.

It seems to be working pretty well as of last night. I want to look at it tomorrow afternoon as all my stocks, bar 1, are in London and the LSE was closed last night.

 

[merlin777]

sounds like there might be an issue with the delimiter you are using.

I'm using googlefinace for data too but I'm on the pc so I don't know if what I'm about to suggest will work for you.

When you publish the googlesheet to web, select the web page option and copy the link it gives you.
In excel go to data/get external data from web and paste the link into the address bar of the window that comes up. Import that table to your chosen location in excel. I get an error pop up but I just ignore it and carry on.

 

[alansidman]

@merlin777

Look at this spreadsheet link. This also uses Google, but it may be more to your liking.

http://investexcel.net/multiple-stock-quote-downloader-for-excel/

 

[merlin777]

Thanks for that but I only need live share prices and not historical data. The method I'm using is very quick and simple. It's just the stock exchange I can't get and that's not critical.

 

[Stiuart_W]

Thanks for that but I only need live share prices and not historical data. The method I'm using is very quick and simple. It's just the stock exchange I can't get and that's not critical.

To be honest I'm rather confused about your requirement. You specify the stock symbol so you MUST know on which exchange it is traded.