Macro to download monthly stock data and to refresh that data with the macro

Anybody? Maybe an easier question would be, how do you make a macro using an excel input of a stock name and a time frame to get data from some query like yahoo finance? because when i try to record the macro for the query process to go to yahoo.finance.com etc, the macro code goes like :

Please Login or Register  to view this content.

I'm trying to make this part :
"URL;http://finance.yahoo.com/q/hp?s=T+Historical+Prices", Destination:=Range _
("$G$3"))
the T in "url" be modifiable by a cell inside of excel. If i directly type in the cell number instead of T in the "url", it doesn't work, anybody? How do i make this macro work automatically in response to the cell name?

Hi Frankdude,

Have you heard of Excel Web Queries? They may do just what you want. Read about them here:
http://articles.techrepublic.com.com...g=feed&subj=tr
or search the net (I use Bing)... Bing the net for "Excel Web Queries"

After you learn more about what Excel already does then perhaps you can request assistance.

Originally Posted by MarvinP

Hi Frankdude,

Have you heard of Excel Web Queries? They may do just what you want. Read about them here:
http://articles.techrepublic.com.com...g=feed&subj=tr
or search the net (I use Bing)... Bing the net for "Excel Web Queries"

After you learn more about what Excel already does then perhaps you can request assistance.

Thanks for that article Marvin, it talks about exactly what i need, but i don't understand the parameters part of the VBA code which is exactly what i need to figure out. The author offers two solutions for post data and get data. My question is, is the data from yahoo.finance post or get data? I think it's get, so the author offers this solution as i understand:

Please Login or Register  to view this content.

So i change URL connection to :

Please Login or Register  to view this content.

but it comes up red. Please, all i need to figure out is how to make the "stocksymbol" part read as if it's an excel cell like cell "G2". Have been trying to do the stuff on the link you gave me for an hour now still can't figure how to make it work in the VBA thing.

Hi Frankdude,
The line (put a space before StockSymbol)

Please Login or Register  to view this content.

recieves an argument that is a stock symbol (like "MSFT" or "DELL") - you knew that.
So in your code you do something like

Please Login or Register  to view this content.

In cell G2 you have "Dell" or "MSFT"

The line of code

Please Login or Register  to view this content.

Adds MSFT onto the string of the URL so

Please Login or Register  to view this content.

is what shows up in your bowser on the URL line.

I hope that makes sense.

I understand that, thanks, but once i compile the code the data is not loading, i think i messed up somewhere, could you help?

Please Login or Register  to view this content.

Frankdude,

You were so close...

See the attached which works. Put the symbol in A1 and click the button.

I think you will be able to work through the Web Queries now.

Good job.

Thank you so much marvin!

Hi marvin, may be u can help me here... I had a similar query and everything works the way u explain above, however i am trying to retrieve Income statement, balance sheet and cash flow data. Now my problem is : The data that it retrieves is Quarterly data and not annual data, although i have written a macro for Annual data . Below is the code :
How do i solve this problem...plz help

At the same time if i replace the stockSymbol variable with a ticker like WMT (Walmart) then it works fine...

Public Sub GetHistoricalStockPrices1(ByVal StockSymbol As String)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/is?s=" & StockSymbol & "+Income+Statement&annual", Destination _
:=Range("$c$3"))
.Name = "is?s=A+Income+Statement&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/bs?s=" & StockSymbol & "+Balance+Sheet&annual", Destination:= _
Range("$I$3"))
.Name = "bs?s=B+Balance+Sheet&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/cf?s=" & StockSymbol & "+Cash+Flow&annual", Destination:= _
Range("$O$3"))
.Name = "cf?s=C+Cash+Flow&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub
Sub RunFromButtonPress1()
GetHistoricalStockPrices1 (ActiveSheet.Range("a1"))
End Sub

i am trying to retrieve Income statement, balance sheet and cash flow data. Now my problem is : The data that it retrieves is Quarterly data and not annual data, although i have written a macro for Annual data . Below is the code :
How do i solve this problem...plz help

At the same time if i replace the stockSymbol variable with a ticker like WMT (Walmart) then it works fine...

Public Sub GetHistoricalStockPrices1(ByVal StockSymbol As String)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/is?s=" & StockSymbol & "+Income+Statement&annual", Destination _
:=Range("$c$3"))
.Name = "is?s=A+Income+Statement&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/bs?s=" & StockSymbol & "+Balance+Sheet&annual", Destination:= _
Range("$I$3"))
.Name = "bs?s=B+Balance+Sheet&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/cf?s=" & StockSymbol & "+Cash+Flow&annual", Destination:= _
Range("$O$3"))
.Name = "cf?s=C+Cash+Flow&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub
Sub RunFromButtonPress1()
GetHistoricalStockPrices1 (ActiveSheet.Range("a1"))
End Sub