stock downloading from yahoo in excel using vba

hinchah

Board Regular
Joined
Oct 24, 2002
Messages
74
i know this may be lengthy, but i figured someone must have already done this.

rows 1 and 2 are used for my parameters.
row 1 being used for descriptions: Ticker Symbol, Start Date, End Date, and pulling in the close price and volume for each ticker symbol with the selected date range. row 2 being used for entering the parameter info.
row 2 yould read (as an example)

MSFT, 1/1/2002, 1/1/2003, close price, volume.

For each ticker in column A, add a separate wksheet named the ticker symbol and pull in the corresponding info.

So the final result for MSFT, would be an added wksheet named MSFT, with the colums headers being the close and volume, and the rows being the date parameters set in row 2.

all using yahoo finance.

thanks for the help.
i cant seem to find code that will let me link to yahoo?? any help is greatly appreciated.
 
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
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Yes, that worked. I really appreciate it. I had tried a lot of combinations but didn't see that one.

Kind regards, Dwight
 
Upvote 0
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.

PHP:
         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!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top