How to do a web query with VB and dynamic URL yahoo finance

[apwdweb2003]

Hi,

Thank you for all the posts on how to draw data from yahoo finance. I'm kinda new to VB and what i'm trying to do is:

Download the table (Date, price, hi, low, volume) ONLY from on this webpage.

http://finance.yahoo.com/q/hp?s=SSTI (There is a link at the bottom which allows one to download the data into a spreadsheet)


I currently have code that will draft the entire page but all i need is just this specific table. imported into sheet 2.

'Retrieve Variables to place inside a URL String
Sub getVars()
'Declare Variables for URL
Dim myURL As String
Dim Ticker As String
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim f As String
Dim g As String

'Assign values from Cells to variables
Ticker = Cells(1, 1).Value
a = Cells(3, 1).Value
b = Cells(3, 2).Value
c = Cells(3, 3).Value
d = Cells(4, 1).Value
e = Cells(4, 2).Value
f = Cells(4, 3).Value
g = Cells(6, 1).Value

'Append variables of URL to one string myURL
myURL = "http://finance.yahoo.com/q/hp?s="
myURL = myURL + Ticker + "&a="
myURL = myURL + a + "&b="
myURL = myURL + b + "&c="
myURL = myURL + c + "&d="
myURL = myURL + d + "&e="
myURL = myURL + e + "&f="
myURL = myURL + f + "&g="
myURL = myURL + g

'Test Display string myURL in a cell
Cells(15, 1).Value = myURL

'Some code i copied that will display the WHOLE HTML page in sheet 2
Dim WebCopy As Object
Set WebCopy = Sheets("Sheet2")

WebCopy.Cells.Clear

With WebCopy.QueryTables.Add(Connection:="URL;" & myURL, Destination:=WebCopy.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'With Application
'.ScreenUpdating = True
'.DisplayAlerts = True
'.Calculation = xlCalculationAutomatic
'.Goto Range("A1"), True
'End With

End Sub

I tried doing a webquery except i can't cut and paste from excel into the webquery. Plus how do i get around the pop up that asks me if i want to open the file or save the file?

**I"ve tried some of the code from previous posts but can't seem to get it to work right. **

Would greatly appreciate any advice and help i can get from the experienced on this board.

Thanks in advance.

 

[Andrew Poulsom]

Re: How to do a web query with VB and dynamic URL yahoo fina

What does the URL string look like? It should look something like this:

http://table.finance.yahoo.com/table.csv?s=SSTI&a=10&b=21&c=2003&d=11&e=27&f=2003&g=d&ignore=.csv

That's from 21/11/2003 to 27/12/2003 - the month is zero based.

 

[apwdweb2003]

Re: How to do a web query with VB and dynamic URL yahoo fina

Hi Andrew,

Thank you for your help so far. I adjusted my code to produce a string of your specification by picking up variables in various cells on my spreadsheet. Now the challenge is how do I do a webquery to pull the full data from this URL?

I tried to do a copy and paste into the webquery unfortunately the memory of a copy is lost once i enter the webquery function.

Your advice is Greatly appreciated.

Thank you.

 

[Andrew Poulsom]

Re: How to do a web query with VB and dynamic URL yahoo fina

Copy the actual text of the Hyperlink by selecting it in the Formula Bar. It will still be on the clipboard when you are in the Web Query dialog box.

Alternatively pass the text of the Hyperlink to the QueryTables.Add statement in your posted code.