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").Value, 1))

'Historical Data
With ActiveSheet.QueryTables.Add(Connection:= _
    "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"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = 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.

bengoaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dragonjimCommented:
Its been sometime since I programmed... but I don't see any references to WINSOCK API, allowing the program to access protocols.
0
bengoaAuthor Commented:
hope someone here is more up to date with VBA Excel coding who can help me with this problem.   thanks for your try though....
0
CSLARSENCommented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
have you tried changing your URL for something like:

strURL = "http://chart.yahoo.com/table.csv?" & _
                 "s=" & pstrSymbol & _
                 "&a=" & pdtmStart.Month - 1 & _
                 "&b=" & pdtmStart.Day & _
                 "&c=" & pdtmStart.Year & _
                 "&d=" & pdtmEnd.Month - 1 & _
                 "&e=" & pdtmEnd.Day & _
                 "&f=" & pdtmEnd.Year
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NYQuicksaleCommented:
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.