stock downloading from yahoo in excel using vba

hinchah

Board Regular
Joined
Oct 24, 2002
Messages
74
try this, heres the code i used...it's real basic, but it gets the job done...simple and quick...
open a new workbook, insert this code, and save the new book as an add in. if you dont know about add ins, let me know, its real simple.
let me know if you have problems.

ah


in module 1:

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Dwight said:
Hello,

I would like to use this code but I can't get it to work. I am running Windows XP and Office 2002.

I created a workbook with a sheet named Template and set up the columns as shown.

The macro sets up the first sheet but then I get the following:

System Error &H80070057 (-2147024809) The parameter is incorrect.

There is no data in the sheet.

I am not a VBA expert but I went to the Yahoo Website and checked the code's references and they look okay. I noticed the first array argument is (1,4) which seems out of pattern. But when I substituted (1,1) it did not help.

I would really like to have the capabiltiy this code offers. Where am I going wrong?

Thanks in advance for any help.
What happens if you paste this into a new web query and run it manually?

http://table.finance.yahoo.com/table.csv?s=YHOO&a=01&b=1&c=2002&d=00&e=1&f=2003&g=d&ignore=.csv

That's all the code does really.
 

hinchah

Board Regular
Joined
Oct 24, 2002
Messages
74
dwight,

if you tried to use my last post, dont forget to change the line below as andrew stated a few posts ago.

Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)

ah
 

Dwight

New Member
Joined
Nov 20, 2003
Messages
30
Thank you, thank you, and thank you. I don't want to sound overdramatic but you can't comprehend how valuable this is to me.

I did not know about creating your own add-ins but I managed it.

I feel like I should give something back. Another useful feature I found was Microsoft's MSNQuote function add-in. It only works for XP but it allows you to easily retrieve info from their website. So check that out if you like.

As a minor sidenote I really only need the date and adjusted close price. Is there away to only pull those columns. Don't bother with it if you don't want because now I'm just being greedy.

Kind regards,
Dwight
 

Dwight

New Member
Joined
Nov 20, 2003
Messages
30
I have been using the code provided in this post and it works well. Now I have added another column (D) called Period that I would like the user to be able to control whether the data is daily = d or monthly = m.

Here is the code as I have modified it. I added the variable Period as string which should be for the variable g in the web query. I can't seem to get the syntax correct.

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 Period As String
Dim a, b, c, d, e, f
Dim StrURL As String
Set Sh = Worksheets("Template")
Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
For Each Cell In Rng
Ticker = Cell.Value
Period = Cell.Offset(0, 3).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=" & Period
StrURL = StrURL & "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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Should this line:

StrURL = StrURL & "&f=" & f & "g=" & Period

be?

StrURL = StrURL & "&f=" & f & "&g=" & Period

Yahoo expects an ampersand between each equals clause, as in the original code.
 

Dwight

New Member
Joined
Nov 20, 2003
Messages
30
Thanks for the suggestion Andrew but it doesn't fix it. It just downloads daily. I've tried lots of combinations with the quotes and the ampersands but I can't seem to get it to pick up the "m."

But like I said if I go into the original code and replace the d with an m in this line:

StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"

it works perfectly. I don't know why it won't accept my variable as a substitution.

Dwight
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The original code had:

StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"

So substituting your variable like this should work:

StrURL = StrURL & "&f=" & f & "&g=" & Period & "&ignore=.csv"

I think it's just that you are missing ampersands fro the actual URL.
 

Dwight

New Member
Joined
Nov 20, 2003
Messages
30
Yes, that worked. I really appreciate it. I had tried a lot of combinations but didn't see that one.

Kind regards, Dwight
 

eggsell22

New Member
Joined
Jul 14, 2011
Messages
8
I am very new to VBA (almost two weeks) and I think I see what the code is doing. I am trying to modify it so that instead of having the data outputed to new worksheets the values would be outputted next to the symbol in the "Templates" sheet. I am only looking to get closing values for a specific hitorical date.


I think I have to modify the following line of the code "ActiveWorkbook.Worksheets.Add.Name = Ticker" but can't figure out what to change it to. The end the result would look something like this.

Code:
         A          B           C         D      E     F      G          H           I
1      Ticker   Start Date   End Date   Open   High   Low   Close      Volume      Adj Close
2       MSFT   11/10/2003 11/10/2003    26.12  26.23   26     26       54579100     20.33
3       AAPL   11/10/2003 11/10/2003    22.45  22.65   21.84  21.9     8367000      10.95
4        F     11/10/2003 11/10/2003    12.23  12.26   12.18  12.22    9338200      11.02
Is my preffered output configuration even possible? Any help would be greatly appreciated.

Thanks for your time!
 

Forum statistics

Threads
1,081,575
Messages
5,359,711
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top