Attn: VBA Yahoo Finance Historical Quotes

  • Thread starter Thread starter jason
  • Start date Start date
wrt:http://fransking.blogspot.com/2006/06/getting-stock-prices-into-excel....

i am attempting to alter the code as to be able to retrieve historical
data for one equity at a time.

any assistance would be fantastic.

thank you
Click to expand...

finance.yahoo.com/d/tables.csv?
s=GS&d=10&e=13&f=2009&g=d&a=10&b=10&c=2006&ignore=.csv

using

public My_Func()

Set oXMLHttp = CreateObject("MSXML2.ServerXMLHTTP")

oxmlhttp.open "GET", _
"finance.yahoo.com/d/tables.csv?
s=GS&d=10&e=13&f=2009&g=d&a=10&b=10&c=2006&ignore=.csv" _
, False

result=oxmlhttp.responcetext

My_Func=result


this is the basic methodology currently.
 
Jason,

Are you answering your own question with your second post?

What type of historical data do you want to retrieve and for what time frame
do you want to retrieve it? Once you get the responseText, as noted with
your My_Func below, it's pretty easy to split the data by the line feed
character and then by commas. So, again, what exactly are you trying to do
(i.e. loop through a set of tickers, return all available pricing, return the
close price for one day, etc.)?

Best,

Matthew Herbert
 
Jason,

Also, you may want to check out the smf_addin by Randy Harmelink, which is
found on Yahoo! groups.

Best,

Matthew Herbert
 
Jason,

Are you answering your own question with your second post?  

What type of historical data do you want to retrieve and for what time frame
do you want to retrieve it?  Once you get the responseText, as noted with
your My_Func below, it's pretty easy to split the data by the line feed
character and then by commas.  So, again, what exactly are you trying to do
(i.e. loop through a set of tickers, return all available pricing, returnthe
close price for one day, etc.)?

Best,

Matthew Herbert
Click to expand...

Matthew,
Not a solution was just showing my methodology.
The dates do not matter (A,B st B>A) etc, ticker is arbitrary.

Method above does not work.
One ticker at a time is fine, i am able enough as a coder once i have
a single example of a historical pull to generalize.
Thank you very much!
 
Jason,

Also, you may want to check out the smf_addin by Randy Harmelink, which is
found on Yahoo! groups.

Best,

Matthew Herbert
Click to expand...

Matthew,
I saw that, but was unable to find the download link as to peek under
the hood. Any help with either of my issues would be largely
appreciated.
 
Contact me privately and I'll send you a file of mine that gets historical
quotes. I will not respond to requests made in the ng.

It allows you to input as many symbols as desired>select the dates
desired>select monthly,weekly or daily>allows charting of one or all symbols
in the field.
 
wrt:http://fransking.blogspot.com/2006/06/getting-stock-prices-into-excel....

i am attempting to alter the code as to be able to retrieve historical
data for one equity at a time.

any assistance would be fantastic.

thank you
Click to expand...

Jason...The following code will retrieve the source code behind the
Yahoo finance page that shows a complete stock quote for IBM on
September 12, 2002. The source code is assigned to the variable
my_var. This method does not require you to open, navigate or close
an IE window. Therefor it will run much faster than a method using
IE; this will be advantageous if you need to perform multiple quotes
or dates. This model can be elaborated to run any number of quotes
and / or dates. You can use functions such as instr and mid to parse
my_var and extract the information you want. Once extracted, the
information can be placed in a worksheet...Ron

Sub Quotes()
my_url = "http://finance.yahoo.com/q/hp?
s=IBM&a=08&b=12&c=2002&d=08&e=12&f=2002&g=d"
Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "GET", my_url, False
my_obj.send
my_var = my_obj.responsetext
Set my_obj = Nothing
End Sub
 
Jason,

As for the smf_addin:
The smf_addin requires you to setup a Yahoo! profile and subscribe to the
group. Once you subscribe, you will have access to the download links.

As for the Yahoo! download:
I've provided a function below that illustrates the XMLHTTP you are looking
to mimic. Simply run "Test" to see how it works. The result of "Test" is
printed to the Immediate Window (View | Immediate Window).

Best,

Matt

Sub Test()
Dim strText As String
Dim strURL As String
strURL =
"http://ichart.finance.yahoo.com/table.csv?s=APOL&a=10&b=13&c=2004&d=10&e=13&f=2009&g=d"
strText = GetYahooPricingAsString(strURL)
Debug.Print strText
End Sub

Function GetYahooPricingAsString(strURL As String) As String
'---------------------------------------------------------------------
'INFO: 11/12/2009, Matthew Herbert
'---------------------------------------------------------------------
'PURPOSE: This will go to Yahoo!'s website and pull down a stock's
' webpage pricing data from the specified URL (using
' XMLHTTP) and return the result as a string.
'
'strURL The full URL path for the ticker. It is recommended that
' the ConstructYahooURL function be used to create the
' appropriate strURL. - I didn't include ConstructYahooURL in this
post.
'---------------------------------------------------------------------

Dim objXMLHTTP As Object
Dim strText As String

'create the XMLHTTP object
Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")

'query the server
With objXMLHTTP
.Open "GET", strURL, False
.send
strText = .responseText
End With

'return the result
If objXMLHTTP.statusText = "OK" Then
GetYahooPricingAsString = strText
Else
GetYahooPricingAsString = ""
End If

End Function
 
Back
Top