Is the best way to download stocks from Yahoo using Excel and VBA?

Learn the Secret

Get  our 2 Free Books

Get these now which land directly to their inbox.
Invalid email address
(Last Updated On: October 20, 2010)

Is the best way to download stocks from Yahoo using  Excel and VBA?

This forum item was posted:

http://www.mrexcel.com/forum/showthread.php?t=66516&page=2

This is also from Lokesh Madan on Linked In:

Sub Auto_Open()

MenuBars(xlWorksheet).Menus.Add _
Caption:=”Stock_Info”

Set menuitemadded = MenuBars(xlWorksheet).Menus(“Stock_Info”).MenuItems _
.Add(Caption:=”Template”, _
OnAction:=”add_sheets”, _
before:=1)

Set menuitemadded = MenuBars(xlWorksheet).Menus(“Stock_Info”).MenuItems _
.Add(Caption:=”Download”, _
OnAction:=”download”, _
before:=2)
End Sub

Sub Auto_Close()
MenuBars(xlWorksheet).Menus(“Stock_Info”).Delete
End Sub

In module 2….

Sub add_sheets()
Application.ScreenUpdating = False
Workbooks.Add
Worksheets(1).Activate
Worksheets(1).Name = (“Template”)
Application.DisplayAlerts = False
Worksheets(2).Delete
Worksheets(2).Delete
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Ticker”
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “Start Date”
Range(“C1”).Select
ActiveCell.FormulaR1C1 = “End Date”
Range(“A1:C1”).Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Range(“A1:C1”).Select
Selection.Interior.ColorIndex = xlNone
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Columns(“A:C”).Select
With Selection
.HorizontalAlignment = xlCenter
End With
Range(“B2:C200”).Select
Selection.NumberFormat = “m/d/yy”
Range(“A1”).Select
Cells.Select
Cells.EntireColumn.AutoFit
Range(“A2”).Select

End Sub

Sub Download()
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim Ticker As String
Dim StartDate As Date
Dim EndDate As Date
Dim a, b, c, d, e, f
Dim StrURL As String
Set Sh = Worksheets(“Template”)
Set Rng = Sh.Range(“A2:A” & Sh.Range(“A2”).End(xlDown).Row)
For Each Cell In Rng
Ticker = Cell.Value
StartDate = Cell.Offset(0, 1).Value
EndDate = Cell.Offset(0, 2).Value
a = Format(Month(StartDate) – 1, “00”) ‘ Month minus 1
b = Day(StartDate)
c = Year(StartDate)
d = Format(Month(EndDate) – 1, “00”)
e = Day(EndDate)
f = Year(EndDate)
StrURL = “URL; http://table.finance.yahoo.com/table.csv ?”
StrURL = StrURL & “s=” & Ticker & “&a=” & a & “&b=” & b
StrURL = StrURL & “&c=” & c & “&d=” & d & “&e=” & e
StrURL = StrURL & “&f=” & f & “&g=d&ignore=.csv”
ActiveWorkbook.Worksheets.Add.Name = Ticker
With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range(“A1”))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
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, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Range(“A2”).Select
Range(Selection, Selection.End(xlDown)).NumberFormat = “d-mmm-yy”
Columns(“A:F”).EntireColumn.AutoFit
Next Cell
Sheets(“Template”).Select
End Sub

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!
This entry was posted in Quant Development and tagged , , , , on by .

About caustic

Hi i there My name is Bryan Downing. I am part of a company called QuantLabs.Net This is specifically a company with a high profile blog about technology, trading, financial, investment, quant, etc. It posts things on how to do job interviews with large companies like Morgan Stanley, Bloomberg, Citibank, and IBM. It also posts different unique tips and tricks on Java, C++, or C programming. It posts about different techniques in learning about Matlab and building models or strategies. There is a lot here if you are into venturing into the financial world like quant or technical analysis. It also discusses the future generation of trading and programming Specialties: C++, Java, C#, Matlab, quant, models, strategies, technical analysis, linux, windows P.S. I have been known to be the worst typist. Do not be offended by it as I like to bang stuff out and put priorty of what I do over typing. Maybe one day I can get a full time copy editor to help out. Do note I prefer videos as they are much easier to produce so check out my many video at youtube.com/quantlabs