ShaunC
12-05-2011, 06:13 PM
Hi all
I am trying to adapt an existing spreadsheet to obtain data from Yahoo Finance. The original spreadsheet works flawlessly as does my copy when the range from A7:AXXXX follows the format yyy (yyy being stock codes for the US exchange). However, I need to pull the data from the same site but from the Australian exchange. To do this all the stock codes become yyy.ax. The .ax signifies the Australian Stock Exchange (ASX).
The macro works when codes entered (in the yyy.ax) are in the range A7:A145, but when the ASX codes are entered from A146 onward I get the run time error:
Run-time error '1004':
An unexpected error has occurred.
When I click on the Debug button the following is highlighted in yellow:
.Refresh BackgroundQuery:=False
I have copied the code below:
Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer, iMax As Integer
Clear
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic
Set DataSheet = ActiveSheet
For iMax = 0 To 1000 Step 200
i = 7 + iMax
If Cells(i, 1) = "" Then
GoTo stopHere
End If
qurl = "removed link" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> "" And i < iMax + 207
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("B2")
Range("b1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("N7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("N7:N207").Select
Selection.TextToColumns Destination:=Range("N7"), 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), Array(8, 1), Array(9, 1), Array(10, 1))
Range("N7:W207").Select
Selection.Copy
Cells(7 + iMax, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Range("N7:W207").Select
' Selection.ClearContents
Next iMax
With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With
'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' Columns("C:C").ColumnWidth = 25.43
' Range("h2").Select
stopHere:
Clear2
End Sub
Sub Clear()
'
' Clear Macro
'
'
Range("C7:L1200").Select
Selection.ClearContents
End Sub
Sub Clear2()
'
' clear2 Macro
'
'
Columns("N:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
Sub doALL()
Sheets("Yahoo1").Select
GetData
Sheets("Yahoo2").Select
GetData
Sheets("Yahoo3").Select
GetData
End Sub
Sub move()
'
' move Macro
'
'
Range("K7:R207").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
Range("C7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Sub Txt_Col()
'
' Txt_Col Macro
'
'
Range("C7:C480").Select
Selection.TextToColumns Destination:=Range("C7"), 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), Array(8, 1))
End Sub
The original code can be found by searching for "Yahoo3.xls" and saving the file in the second link. A link for Gummy-Stuff (the original author) can be found with this search also.
Any assistance that can be provided would be greatly appreciated.
:beerchug:
Shaun
I am trying to adapt an existing spreadsheet to obtain data from Yahoo Finance. The original spreadsheet works flawlessly as does my copy when the range from A7:AXXXX follows the format yyy (yyy being stock codes for the US exchange). However, I need to pull the data from the same site but from the Australian exchange. To do this all the stock codes become yyy.ax. The .ax signifies the Australian Stock Exchange (ASX).
The macro works when codes entered (in the yyy.ax) are in the range A7:A145, but when the ASX codes are entered from A146 onward I get the run time error:
Run-time error '1004':
An unexpected error has occurred.
When I click on the Debug button the following is highlighted in yellow:
.Refresh BackgroundQuery:=False
I have copied the code below:
Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer, iMax As Integer
Clear
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic
Set DataSheet = ActiveSheet
For iMax = 0 To 1000 Step 200
i = 7 + iMax
If Cells(i, 1) = "" Then
GoTo stopHere
End If
qurl = "removed link" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> "" And i < iMax + 207
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("B2")
Range("b1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("N7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("N7:N207").Select
Selection.TextToColumns Destination:=Range("N7"), 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), Array(8, 1), Array(9, 1), Array(10, 1))
Range("N7:W207").Select
Selection.Copy
Cells(7 + iMax, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Range("N7:W207").Select
' Selection.ClearContents
Next iMax
With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With
'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' Columns("C:C").ColumnWidth = 25.43
' Range("h2").Select
stopHere:
Clear2
End Sub
Sub Clear()
'
' Clear Macro
'
'
Range("C7:L1200").Select
Selection.ClearContents
End Sub
Sub Clear2()
'
' clear2 Macro
'
'
Columns("N:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
Sub doALL()
Sheets("Yahoo1").Select
GetData
Sheets("Yahoo2").Select
GetData
Sheets("Yahoo3").Select
GetData
End Sub
Sub move()
'
' move Macro
'
'
Range("K7:R207").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
Range("C7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Sub Txt_Col()
'
' Txt_Col Macro
'
'
Range("C7:C480").Select
Selection.TextToColumns Destination:=Range("C7"), 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), Array(8, 1))
End Sub
The original code can be found by searching for "Yahoo3.xls" and saving the file in the second link. A link for Gummy-Stuff (the original author) can be found with this search also.
Any assistance that can be provided would be greatly appreciated.
:beerchug:
Shaun