I have been running a macro for eight months now that pulls current quotes
from Yahoo and puts them in a spreadsheet. For reasons unknown, the first
quote, S&P500 index, has stopped functioning. The macro returns the name of
the index as "%5EGSPC" whereas it should be "S&P500". The ticker, ^GSPC,
works on their website. I do not believe I've made macro changes that would
affect this in any way.

Please advise if you've encountered this problem and, better yet, a
solution. Thanks, John

code starts he

Sub GetData2()

Dim rCell As Range, rSymb As Range
Dim sYahooUrl As String
Dim qt As QueryTable
Dim rQtStart As Range

Set rQtStart = Sheet1.Range("A40")
Set rSymb = Sheet1.Range("B2", Sheet1.Range("B29").End(xlUp))
sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"

'build url with column B
For Each rCell In rSymb.Cells
sYahooUrl = sYahooUrl & "+" & rCell.Value
Next rCell

sYahooUrl = sYahooUrl & "&f=nl1c"

'get a query table if it exits
On Error Resume Next
Set qt = rQtStart.QueryTable
On Error GoTo 0

If qt Is Nothing Then 'create new
Set qt = Sheet1.QueryTables.Add(sYahooUrl, rQtStart)
Else 'update existing
qt.Connection = sYahooUrl
End If
On Error Resume Next
qt.Refresh False

'parse results
Application.DisplayAlerts = False
qt.ResultRange.TextToColumns _
Destination:=qt.ResultRange.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Comma:=True
Application.DisplayAlerts = True

'reschedule update
StartTimer
Debug.Print Sheet1.QueryTables.Count

End Sub

try
"http://download.finance.yahoo.com/d/quotes.csv?s=^GSPC"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John V" wrote in message
...
I have been running a macro for eight months now that pulls current quotes
from Yahoo and puts them in a spreadsheet. For reasons unknown, the first
quote, S&P500 index, has stopped functioning. The macro returns the name
of
the index as "%5EGSPC" whereas it should be "S&P500". The ticker, ^GSPC,
works on their website. I do not believe I've made macro changes that
would
affect this in any way.

Please advise if you've encountered this problem and, better yet, a
solution. Thanks, John

code starts he

Sub GetData2()

Dim rCell As Range, rSymb As Range
Dim sYahooUrl As String
Dim qt As QueryTable
Dim rQtStart As Range

Set rQtStart = Sheet1.Range("A40")
Set rSymb = Sheet1.Range("B2", Sheet1.Range("B29").End(xlUp))
sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"

'build url with column B
For Each rCell In rSymb.Cells
sYahooUrl = sYahooUrl & "+" & rCell.Value
Next rCell

sYahooUrl = sYahooUrl & "&f=nl1c"

'get a query table if it exits
On Error Resume Next
Set qt = rQtStart.QueryTable
On Error GoTo 0

If qt Is Nothing Then 'create new
Set qt = Sheet1.QueryTables.Add(sYahooUrl, rQtStart)
Else 'update existing
qt.Connection = sYahooUrl
End If
On Error Resume Next
qt.Refresh False

'parse results
Application.DisplayAlerts = False
qt.ResultRange.TextToColumns _
Destination:=qt.ResultRange.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Comma:=True
Application.DisplayAlerts = True

'reschedule update
StartTimer
Debug.Print Sheet1.QueryTables.Count

End Sub

No soap. This change returns an empty array. With quote.yahoo.com, I do
continue to get other stock quotes that follow without problem. It's just S&P.

I'll post if I solve the mystery.

"Don Guillett" wrote:

try
"http://download.finance.yahoo.com/d/quotes.csv?s=^GSPC"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John V" wrote in message
...
I have been running a macro for eight months now that pulls current quotes
from Yahoo and puts them in a spreadsheet. For reasons unknown, the first
quote, S&P500 index, has stopped functioning. The macro returns the name
of
the index as "%5EGSPC" whereas it should be "S&P500". The ticker, ^GSPC,
works on their website. I do not believe I've made macro changes that
would
affect this in any way.

Please advise if you've encountered this problem and, better yet, a
solution. Thanks, John

code starts he

Sub GetData2()

Dim rCell As Range, rSymb As Range
Dim sYahooUrl As String
Dim qt As QueryTable
Dim rQtStart As Range

Set rQtStart = Sheet1.Range("A40")
Set rSymb = Sheet1.Range("B2", Sheet1.Range("B29").End(xlUp))
sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"

'build url with column B
For Each rCell In rSymb.Cells
sYahooUrl = sYahooUrl & "+" & rCell.Value
Next rCell

sYahooUrl = sYahooUrl & "&f=nl1c"

'get a query table if it exits
On Error Resume Next
Set qt = rQtStart.QueryTable
On Error GoTo 0

If qt Is Nothing Then 'create new
Set qt = Sheet1.QueryTables.Add(sYahooUrl, rQtStart)
Else 'update existing
qt.Connection = sYahooUrl
End If
On Error Resume Next
qt.Refresh False

'parse results
Application.DisplayAlerts = False
qt.ResultRange.TextToColumns _
Destination:=qt.ResultRange.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Comma:=True
Application.DisplayAlerts = True

'reschedule update
StartTimer
Debug.Print Sheet1.QueryTables.Count

End Sub

The problem seems to be the carat symbol. Any index prefixed with a "^" is
not interpreted properly. It translates the carat into "%5E" when returning
the name of the index.

Any alternative methods of designating a "^" in VBA?

"John V" wrote:

No soap. This change returns an empty array. With quote.yahoo.com, I do
continue to get other stock quotes that follow without problem. It's just S&P.

I'll post if I solve the mystery.

"Don Guillett" wrote:

try
"http://download.finance.yahoo.com/d/quotes.csv?s=^GSPC"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John V" wrote in message
...
I have been running a macro for eight months now that pulls current quotes
from Yahoo and puts them in a spreadsheet. For reasons unknown, the first
quote, S&P500 index, has stopped functioning. The macro returns the name
of
the index as "%5EGSPC" whereas it should be "S&P500". The ticker, ^GSPC,
works on their website. I do not believe I've made macro changes that
would
affect this in any way.

Please advise if you've encountered this problem and, better yet, a
solution. Thanks, John

code starts he

Sub GetData2()

Dim rCell As Range, rSymb As Range
Dim sYahooUrl As String
Dim qt As QueryTable
Dim rQtStart As Range

Set rQtStart = Sheet1.Range("A40")
Set rSymb = Sheet1.Range("B2", Sheet1.Range("B29").End(xlUp))
sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"

'build url with column B
For Each rCell In rSymb.Cells
sYahooUrl = sYahooUrl & "+" & rCell.Value
Next rCell

sYahooUrl = sYahooUrl & "&f=nl1c"

'get a query table if it exits
On Error Resume Next
Set qt = rQtStart.QueryTable
On Error GoTo 0

If qt Is Nothing Then 'create new
Set qt = Sheet1.QueryTables.Add(sYahooUrl, rQtStart)
Else 'update existing
qt.Connection = sYahooUrl
End If
On Error Resume Next
qt.Refresh False

'parse results
Application.DisplayAlerts = False
qt.ResultRange.TextToColumns _
Destination:=qt.ResultRange.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Comma:=True
Application.DisplayAlerts = True

'reschedule update
StartTimer
Debug.Print Sheet1.QueryTables.Count

End Sub

CHAR(94) returns a "^" can you bake that into your string?

As an alternative, have you tried this add-in?

http://finance.groups.yahoo.com/group/smf_addin/

--JP

On Oct 20, 2:00*pm, John V wrote:
The problem seems to be the carat symbol. Any index prefixed with a "^" is
not interpreted properly. It translates the carat into "%5E" when returning
the name of the index.

Any alternative methods of designating a "^" in VBA?

'sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"
sYahooUrl = "URL;http://download.finance.yahoo.com/d/quotes.csv?s=^GSPC"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
try
"http://download.finance.yahoo.com/d/quotes.csv?s=^GSPC"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John V" wrote in message
...
I have been running a macro for eight months now that pulls current quotes
from Yahoo and puts them in a spreadsheet. For reasons unknown, the first
quote, S&P500 index, has stopped functioning. The macro returns the name
of
the index as "%5EGSPC" whereas it should be "S&P500". The ticker, ^GSPC,
works on their website. I do not believe I've made macro changes that
would
affect this in any way.

Please advise if you've encountered this problem and, better yet, a
solution. Thanks, John

code starts he

Sub GetData2()

Dim rCell As Range, rSymb As Range
Dim sYahooUrl As String
Dim qt As QueryTable
Dim rQtStart As Range

Set rQtStart = Sheet1.Range("A40")
Set rSymb = Sheet1.Range("B2", Sheet1.Range("B29").End(xlUp))
sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"

'build url with column B
For Each rCell In rSymb.Cells
sYahooUrl = sYahooUrl & "+" & rCell.Value
Next rCell

sYahooUrl = sYahooUrl & "&f=nl1c"

'get a query table if it exits
On Error Resume Next
Set qt = rQtStart.QueryTable
On Error GoTo 0

If qt Is Nothing Then 'create new
Set qt = Sheet1.QueryTables.Add(sYahooUrl, rQtStart)
Else 'update existing
qt.Connection = sYahooUrl
End If
On Error Resume Next
qt.Refresh False

'parse results
Application.DisplayAlerts = False
qt.ResultRange.TextToColumns _
Destination:=qt.ResultRange.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Comma:=True
Application.DisplayAlerts = True

'reschedule update
StartTimer
Debug.Print Sheet1.QueryTables.Count

End Sub