Private Sub CommandButton1_Click()
Application.displayalerts=False
Dim Symb As String
Dim startdate As Date
Dim enddate As Date
Dim a, b, c, d, e, f As Integer
' in the line above only f is declared as an Integer - all the other variables are
' by default declared as Variants. The reason is that in VBA variables need to be
' declared one by one and cannot be declared as a group.
Dim g As String
'symb = Range("B1").Value
Sheets("Sheet1").Select
startdate = Range("B2").Value
enddate = Range("B3").Value
a = Month(startdate)
b = Day(startdate)
c = Year(startdate)
d = Month(enddate)
e = Day(enddate)
f = Year(enddate)
g = LCase(Left(Range("B4").Value, 1))
For i = 1 To 50
Sheets("Sheet1").Select
Symb = Range("a1").Offset(0, i).Value
'Historical Data
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & Symb & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=" & g _
, Destination:=Range("A6"))
.Name = "Quote: " & Symb
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "19"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
'This was false
Sheets("Sheet1").Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
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)), TrailingMinusNumbers:=True
End With
'Call Module1.Display
'Sheets("Sheet1").Range("A6").Select.End(xlDown).Copy Sheets("Sheet2").[A2].Offset(0, i)
'Range(Selection, Selection.End(xlDown)).Select
Sheets("Sheet1").Range("A6:A7000").Copy Sheets("Sheet2").[A2].Offset(0, 0)
Sheets("Sheet1").Range("G6:G7000").Copy Sheets("Sheet2").[A2].Offset(0, i)
Sheets("Sheet1").Range("B1:BW1").Copy Sheets("Sheet2").Range("B1:BW1")
Next i
'this copies dates
Sheets("Sheet1").Range("A7:A7000").Copy Sheets("Sheet2").[A3]
Columns("A:A").ColumnWidth = 12.86
'this formats output cells
'Sheets("Sheet2").[A2] = "Date"
'Sheets("Sheet2").Range("A2:H2").Style = "Calculation"
'copies symbol tickers in the output sheet
Sheets("Sheet1").Range("B1:W1").Copy Sheets("Sheet2").Range("B1:W1")
Sheets("Sheet1").Range("A6").Select
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
Selection.ClearContents
Selection.QueryTable.Delete
Sheets("Sheet1").Range("A6").Select
End Sub
Bookmarks