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?

 

[alansidman]

https://office-watch.com/2016/excel-stock-prices-from-google-finance/

 

[FormR]

https://office-watch.com/2016/excel-stock-prices-from-google-finance/

I've got this working for me, thanks for sharing

Couldn't agree more with this quote from the link.

It’s a sad indictment of Microsoft Office that the best place to get stock data is Google.

 

[igold]

Great. Works like a charm. I took the CSV and parsed it with the same routine that I used for the CSV that Yahoo used to generate. Thank You.

 

[rabbfrfc]

Another method for accessing.

Sub googsub()
strTicker = "FB"
Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As Double
strURL = "https://finance.google.com/finance/historical?q=" & strTicker & "&output=csv"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.send
strCSV = http.responseText
If (strFieldName = "Close") Then intFieldIndex = 4
' The most recent information is in row 2, just below the table headings.
' The price close is the 5th entry
strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows



For i = 1 To UBound(strRows())

c = InStr(CStr(strRows(i)), ",")
If c > 0 Then
strColumns = Split(Replace(strRows(i), ", ", ""), ",")

TICKER = strTicker
DBSDATE = strColumns(0)
DBSYEAR = Year(strColumns(0))
DOP = Val(strColumns(1))
DH = Val(strColumns(2))
DL = Val(strColumns(3))
LT = Val(strColumns(4)) ' 4 means: 5th position, starting at index 0
V = Val(strColumns(5))
Debug.Print TICKER & ",Google,Volume = " & V & " Close = " & DOP & " High = " & DH & " Date: " & DBSDATE


End If
Next i
End Sub

 

[merlin777]

I haven't been able to get this working. Does it work with 2007?

 

[merlin777]

OK - managed to get a table imported into 2007. Looks like it might be simpler than later versions.

Please may I ask 2 questions though?

To get it to keep refreshing (i.e. to get the share prices to update in my excel workbook every few minutes) is it just a case of setting data/connection properties/enable background refresh/refresh every to a couple of minutes? Will that cause my stock priced to update or do i have to have google sheets workbook open too?

Secondly, of all the possible googlefinance attributes, is there one that returns the company name?

thanks for the help.

 

[igold]

If your symbol is in cell A2

=googlefinance(A2,"name")

 

[merlin777]

Thanks - working now. I just had assumed it wouldn't work because the attributes list didn't have a 'name' option and the writer of the article said they ought-to.

When I tried it, it worked perfectly.

Sorry to waste your time.

still need to know if I have to keep google sheets open to get a refresh for live prices in excel?

 

[igold]

I don't know what kind of macro you are using, but if you are having Google publish your sheet as a csv, every time your script calls for the csv it will auto update...

 

[merlin777]

even if my googlesheet isn't open? Wow - that seems too good to be true! I'm not using a macro for this as I was with the yahoo api. I publish my googlesheet (which uses googlefinance() )to the web and using the url it gives me to 'import data from web' into an excel sheet, set to auto refresh every couple of minutes.