M
matt
I have coded a program that takes the ticker symbols from one
worksheet(1) [i.e. A1 = ticker1, A2 = ticker2....], gets the pricing
information from Yahoo!Finance and inputs the pricing information into
another worksheet(2), runs a calculation based off the pricing
information,
and copies and pastes the output to another worksheet(3). When the
calculation is complete the computer clears out the pricing
information from worksheet(2) and moves to the next ticker symbol.
The problem however is that if Yahoo!Finance does NOT have pricing
information for the specified ticker, a small error box pops up that
reads "Could not open "http://ichart.finance.yahoo.com/table.csv?
s= ......." The generated error is a message box that has a white
bubble on the left-hand side and the white bubble has a blue, lower
case "i" in it. This error is generated NOT from VBA but from the
Yahoo!Finance website because the data does NOT exist on Yahoo!s
website. When I click "OK" the program execution returns "Run-time
error '1004': Microsoft Office Excel cannot access the file 'http://
ichart.....' There are serveral possible reasons:....." I'm NOT
worried about the "Run-time error '1004'" because I can code an "On
Error Resume Next" error handler. I am, however, interested in how to
take care of the Yahoo! generated error.
Basically, I want to know if there is a way to click the pop-up "OK"
button for the "Could not open...." message box. (I want to run this
on several ticker symbols, and it would be a pain to have to manually
click the "OK" button each time this type of situation occurs). I'm
sure that there is a way to accomplish this, but I don't know how to
go about doing it, and I wonder if it requires API. (As a side note,
I don't have a background in API, I just kjnow that as a last resort
you can always turn to API to accomplish a task).
I have included my code below. A list of tickers that work as
expected are the following: JNJ, ADBE, APOL, LLTC. A list of tickers
that produce the Yahoo! generated error "Could not open...." message
box are the following: JW.A, HCA, ITWG, KHD, KOSP, and TALK.
Additionally, be sure that the "Workbooks.Open Filename:=" line reads
the following: Workbooks.Open Filename:="http://
ichart.finance.yahoo.com/table.csv?s=" & tic & "&a=" & a & "&b=" & b &
"&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=d&ignore=.csv"
(I'm grabbing the pricing information for the past 10 years, starting
with a date of today and then going back 10 years).
Any help is appreciated. Thanks in advance. (Also, make sure that
VBE: Tools/References/Microsoft Forms 2.0 Object Library is
selected).
Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard
Set myData = New DataObject
wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name
Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count
For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value
mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")
a = monthToNumber(mon)
b = dy
c = yr - 10
d = a
e = b
f = yr
Workbooks.Open Filename:="http://ichart.finance.yahoo.com/
table.csv?s=" & tic & "&a=" & a & "&b=" & b & "&c=" & c &
"&d=" & d & "&e=" & e & "&f=" & f & "&g=d&ignore=.csv"
Workbooks(wbPT).Worksheets(2).Range("a1").CurrentRegion.ClearContents
Workbooks(wbCSV).Activate
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpecial
myData.SetText ""
myData.PutInClipboard
Workbooks(wbCSV).Close savechanges:=False
Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next
End Sub
Private Function monthToNumber(ByVal mon)
Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select
End Function
worksheet(1) [i.e. A1 = ticker1, A2 = ticker2....], gets the pricing
information from Yahoo!Finance and inputs the pricing information into
another worksheet(2), runs a calculation based off the pricing
information,
and copies and pastes the output to another worksheet(3). When the
calculation is complete the computer clears out the pricing
information from worksheet(2) and moves to the next ticker symbol.
The problem however is that if Yahoo!Finance does NOT have pricing
information for the specified ticker, a small error box pops up that
reads "Could not open "http://ichart.finance.yahoo.com/table.csv?
s= ......." The generated error is a message box that has a white
bubble on the left-hand side and the white bubble has a blue, lower
case "i" in it. This error is generated NOT from VBA but from the
Yahoo!Finance website because the data does NOT exist on Yahoo!s
website. When I click "OK" the program execution returns "Run-time
error '1004': Microsoft Office Excel cannot access the file 'http://
ichart.....' There are serveral possible reasons:....." I'm NOT
worried about the "Run-time error '1004'" because I can code an "On
Error Resume Next" error handler. I am, however, interested in how to
take care of the Yahoo! generated error.
Basically, I want to know if there is a way to click the pop-up "OK"
button for the "Could not open...." message box. (I want to run this
on several ticker symbols, and it would be a pain to have to manually
click the "OK" button each time this type of situation occurs). I'm
sure that there is a way to accomplish this, but I don't know how to
go about doing it, and I wonder if it requires API. (As a side note,
I don't have a background in API, I just kjnow that as a last resort
you can always turn to API to accomplish a task).
I have included my code below. A list of tickers that work as
expected are the following: JNJ, ADBE, APOL, LLTC. A list of tickers
that produce the Yahoo! generated error "Could not open...." message
box are the following: JW.A, HCA, ITWG, KHD, KOSP, and TALK.
Additionally, be sure that the "Workbooks.Open Filename:=" line reads
the following: Workbooks.Open Filename:="http://
ichart.finance.yahoo.com/table.csv?s=" & tic & "&a=" & a & "&b=" & b &
"&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=d&ignore=.csv"
(I'm grabbing the pricing information for the past 10 years, starting
with a date of today and then going back 10 years).
Any help is appreciated. Thanks in advance. (Also, make sure that
VBE: Tools/References/Microsoft Forms 2.0 Object Library is
selected).
Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard
Set myData = New DataObject
wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name
Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count
For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value
mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")
a = monthToNumber(mon)
b = dy
c = yr - 10
d = a
e = b
f = yr
Workbooks.Open Filename:="http://ichart.finance.yahoo.com/
table.csv?s=" & tic & "&a=" & a & "&b=" & b & "&c=" & c &
"&d=" & d & "&e=" & e & "&f=" & f & "&g=d&ignore=.csv"
Workbooks(wbPT).Worksheets(2).Range("a1").CurrentRegion.ClearContents
Workbooks(wbCSV).Activate
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpecial
myData.SetText ""
myData.PutInClipboard
Workbooks(wbCSV).Close savechanges:=False
Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next
End Sub
Private Function monthToNumber(ByVal mon)
Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select
End Function