bengoa
asked on
Excel VBA to get Historical Stock Prices
Can anyone tell me why this code is not getting any results??
Sub Download_Data()
Application.ScreenUpdating = False
Dim symb As String
Dim startdate As Date
Dim enddate As Date
Dim a, b, c, d, e, f As Integer
Dim g As String
symb = Range("E2").Value
startdate = Range("B2").Value
enddate = Range("B3").Value
a = Month(startdate) - 1
b = Day(startdate)
c = Year(startdate)
d = Month(enddate) - 1
e = Day(enddate)
f = Year(enddate)
g = LCase(Left(Range("E3").Val ue, 1))
'Historical Data
With ActiveSheet.QueryTables.Ad d(Connecti on:= _
"URL;http://finance.yahoo.com/q/hp?s=" & symb & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=" & g _
, Destination:=Range("A12"))
.Name = "Quote: " & symb
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "21"
.WebPreFormattedTextToColu mns = True
.WebConsecutiveDelimitersA sOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.ScreenUpdating = True
End Sub
Or does anyone have a simple solution that retrieve Historical Stock Prices from yahoo finance?
Many Thanks.
Sub Download_Data()
Application.ScreenUpdating
Dim symb As String
Dim startdate As Date
Dim enddate As Date
Dim a, b, c, d, e, f As Integer
Dim g As String
symb = Range("E2").Value
startdate = Range("B2").Value
enddate = Range("B3").Value
a = Month(startdate) - 1
b = Day(startdate)
c = Year(startdate)
d = Month(enddate) - 1
e = Day(enddate)
f = Year(enddate)
g = LCase(Left(Range("E3").Val
'Historical Data
With ActiveSheet.QueryTables.Ad
"URL;http://finance.yahoo.com/q/hp?s=" & symb & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=" & g _
, Destination:=Range("A12"))
.Name = "Quote: " & symb
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "21"
.WebPreFormattedTextToColu
.WebConsecutiveDelimitersA
.WebSingleBlockTextImport = False
.WebDisableDateRecognition
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.ScreenUpdating
End Sub
Or does anyone have a simple solution that retrieve Historical Stock Prices from yahoo finance?
Many Thanks.
Its been sometime since I programmed... but I don't see any references to WINSOCK API, allowing the program to access protocols.
ASKER
hope someone here is more up to date with VBA Excel coding who can help me with this problem. thanks for your try though....
Hi
Maybe you could get something out of checking this site:
http://fransking.blogspot.com/2006/06/getting-stock-prices-into-excel.html
(Havent tried it though)
cheers
cslarsen
Maybe you could get something out of checking this site:
http://fransking.blogspot.com/2006/06/getting-stock-prices-into-excel.html
(Havent tried it though)
cheers
cslarsen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Like the above code, i've also a code, which works perfectly fine when run manually, but i want to make it dynamic, means the stock symbol,start date and end date to be read from cell h1,i1 and ji respectively by the code and bring in data from website. it can be associated with the worksheet change, but the dates and the stock symbol won't be entered manually,there's formula in h1,i1 and j1, which i want the code to read and bring in data. is that possible? the code is below
Sub GetStock(ByVal stockSymbol As String, ByVal startdate As Date, ByVal enddate As Date)
Dim DownloadURL As String
Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear As String
StartMonth = Format(Month(startdate) - 1, "00")
StartDay = Format(Day(startdate), "00")
StartYear = Format(Year(startdate), "00")
EndMonth = Format(Month(enddate) - 1, "00")
EndDay = Format(Day(enddate), "00")
EndYear = Format(Year(enddate), "00")
DownloadURL = "URL;http://table.finance.yahoo.com/table.csv?s=" + stockSymbol + "&a=" + StartMonth + "&b=" + StartDay + "&c=" + StartYear + "&d=" + EndMonth + "&e=" + EndDay + "&f=" + EndYear + "&g=d&ignore=.csv"
With ActiveSheet.QueryTables.Add(Connection:=DownloadURL, Destination:=Range("$A$1"))
.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 = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-12
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Columns("A:F").EntireColumn.AutoFit
End Sub
Sub Download()
Call GetStock("AAPL", "01/01/2010", "4/24/2010")
End Sub