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?

 

[igold]

If it works, that's great. I guess you'll find out tomorrow...

 

[merlin777]

I'm very excited.

 

[merlin777]

I'm very excited.

Well, my share prices are updating in excel even though I have googlesheets closed.

Amazed and delighted.

 

[igold]

Yeah, it worked for me on Friday... What a relief, I have so much depending on the ability to be able to suck out quotes on demand!

 

[merlin777]

Yeah, it worked for me on Friday... What a relief, I have so much depending on the ability to be able to suck out quotes on demand!

and so much easier than using yahpoo's api with all the vba needed and reliability issues.

I've even managed to get exchange rates out if it.

The only thing i cant do at the moment is get it to give me the exchange the shares are traded on.

 

[alansidman]

For those looking for historical data, here is a downloadable spreadsheet that works with google finance.

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

 

[brynrogersTHG]

I have been using this bit of Excel macro code for some time, only to find it no longer works.
Is there any direct equiv. of this using the Google or any other API that is Free?
This extracts the current price only, I'm not interested in the other info really.

Function Shareprice(YahooTicker As String)
Dim strURL As String, strCSV As Double
' Compile the request URL with selected Yahoo Ticker & Yahoo Tag
' Example for Apple Inc. share price
' http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1
strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & YahooTicker & "&f=l1"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText
Shareprice = strCSV
Set http = Nothing
End Function

Hope someone can help. I don't want to use GoogleSheets as its very slow compared to standalone Excel.

Thanks.. Bryn..

 

[igold]

I have been using this bit of Excel macro code for some time, only to find it no longer works.
Is there any direct equiv. of this using the Google or any other API that is Free?
This extracts the current price only, I'm not interested in the other info really.

Function Shareprice(YahooTicker As String)
Dim strURL As String, strCSV As Double
' Compile the request URL with selected Yahoo Ticker & Yahoo Tag
' Example for Apple Inc. share price
' http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1
strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & YahooTicker & "&f=l1"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText
Shareprice = strCSV
Set http = Nothing
End Function

Hope someone can help. I don't want to use GoogleSheets as its very slow compared to standalone Excel.

Thanks.. Bryn..

Hi Bryn,

Look at the link in Post #11 of this thread and stop at the part where it says post as a .CSV and then let your code take over as the http.response text to CSV that Yahoo was returning... you may want to use VbNewLine to parse with.

I hope this helps

 

[FormR]

stop at the part where it says post as a .CSV and then let your code take over as the http.response text to CSV that Yahoo was returning

I personally went further and used power query to import the csv file directly into Excel - it does the parsing for you and removes the need for any code.

 

[brynrogersTHG]

Hi, and thanks for the response.

I did see this post but it appears to pull historical data and not the 15 minute delayed current price that I'm looking for.
Do you know how to retrieve this data?

rabbit, I tried to respond to your personal email but it seems I have no personal email space, and it would not let me reply.

Thanks, Bryn