?
Solved

Excel VBA to get Historical Stock Prices

Posted on 2008-11-12
5
Medium Priority
?
2,026 Views
Last Modified: 2013-11-25
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.

0
Comment
Question by:bengoa
5 Comments
 
LVL 10

Expert Comment

by:dragonjim
ID: 22943539
Its been sometime since I programmed... but I don't see any references to WINSOCK API, allowing the program to access protocols.
0
 

Author Comment

by:bengoa
ID: 22943775
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
 
LVL 15

Expert Comment

by:CSLARSEN
ID: 22943904
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1500 total points
ID: 22944255
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
 
LVL 1

Expert Comment

by:NYQuicksale
ID: 36049131
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question