how to open a internet file through VBA

[novice_2010]

dear all

I want to describe my question by using an example:

Let's say, I want to get Microsoft stock historical price. I can go to yahoo finance:

http://finance.yahoo.com/q/hp?s=MSFT

and click the "Download To Spreadsheet ",


I can also use Excel VBA to do the same thing:

Sub Macro1()
Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=MSFT&d=2&e=18&f=2010&g=d&a=2&b=13&c=1986&ignore=.csv"
End Sub

Now, my question is: I want to use a variable to denote the stock (MSFT as in the above example) so that I can use this sub to open any stock.
The following is an tentative macro:

Sub Macro1()

dim StockTicker as String

StockTicker = MSFT

Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=StockTicker
&d=2&e=18&f=2010&g=d&a=2&b=13&c=1986&ignore=.csv"

End Sub

However this macro doesn't work for me.

Could anyone please help me how to make this macro work?

 

[Tom Urtis]

In theory it'd be

Sub Macro1()
Dim StockTicker$
StockTicker = "MSFT"
 
Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?s=" & StockTicker & "&d=2&e=18&f=2010&g=d&a=2&b=13&c=1986&ignore=.csv"
End Sub

 

[novice_2010]

Thank you so much, Tom.

By the way, what is variable type for "$", I couldn't find it in my textbook.

 

[Tom Urtis]

The $ is an abbreviated was of saying "as String".

Those abbreviation characters have been around for awhile, here is a general overview of variable types, their abbreviation characters, and their description. The intervening dots are my attempt to enhance readability for the fields on this html page.

Type...............Memory....Character...........Description
Byte...............1 byte....none................Positive whole number ranging from 0 through 255 that can be represented as a binary value.
Boolean............2 bytes...none................True or False value
Integer............2 bytes...%...................Whole numbers ranging from -32,768 through 32,767.
Long...............4 bytes...&...................Whole numbers ranging from -2,147,483,648 through 2,147,483,647.
Single.............4 bytes...!...................Single-precision floating-point number (with decimal points) ranging from -3.402823E38 to 3.402823E38.
Double.............8 bytes...#...................Double-precision floating-point number ranging from -1.79769313486232E308 to 1.79769313486232E308.
Currency...........8 bytes...@...................Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Date...............8 bytes...none................Represents dates from January 1, 100 through December 31, 9999.
Object.............4 bytes...none................An instance of a class or object reference.
String.............10 bytes + 1 byte per char...$...Series of any ASCII characters.
String (fix len)...length of string..............none...Series of any ASCII characters, of a pre-defined length.
Variant............min 16 bytes..................none...Any kind of data except fixed-length String data and user-defined types.
<!-- / message --><!-- sig -->
<!-- / message --><!-- sig -->

 

[novice_2010]

Thank you very much!

 

[Tom Urtis]

We're even up on this one, thank you for that useful codeline to download csv workbooks on historical stock data for a given company. I did not know about that link and have saved it in my library, thanks again.