Import 1, 3, 6, and 12 month returns for a list of mutual funds

[ryguy7272]

Does anyone know of a relatively easy way to get 1, 3, 6, and 12 month
returns for a list of mutual fund symbols, from finance.yahoo.com?

For instance, if I have the following in A3:A7:
RPBAX
TRBCX
PRWCX
PRCOX
PRDMX

Range B2:E2 = 1-month, 3-months, 6-months, 12-months.

Now, for RPBAX, by right-clicking on the page and selecting ‘View Source’,
in the HTML, I see this:
<td class="yfnc_datamodlabel1">1-Month</td><td class="yfnc_datamoddata1"
align="right">4.05</td>
<td class="yfnc_datamodlabel1">3-Month</td><td class="yfnc_datamoddata1"
align="right">6.37</td>
<td class="yfnc_datamodlabel1">1-Year</td><td class="yfnc_datamoddata1"
align="right">31.03</td>

I guess I can loop through the list of funds, something like this:

Sub Import()
Dim str1 As String
Dim c As Range

For Each c In Sheets("Sheet1").Range("A3:A7")
str1 = "URL;http://finance.yahoo.com/q/pm?s=" & _
c.Value
With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("B3"))
.Name = str1
.Name = "ks?s=c.Value"
.WebFormatting = xlWebFormattingNone
.WebTables = "24"
.Refresh BackgroundQuery:=False
End With
Next c

End Sub

This doesn’t work; nothing is imported, and even if it were, the Range("B3")
is where I need to start, but then I need to something like offset(0,1),
Import 3-months, offset(0,1), Import 6-months, and offset(0,1), Import
12-months. Then something like offset(1,-4) to get to cell A4, and find the
returns for that fund. None of this logic is coded into the macro yet.
That’s probably not too hard to do, but I’m not exactly sure how to
incorporate it into the URL. Also, I don’t see the 6-month return anywhere
on the page. So, I’m thinking Yahoo doesn’t cover this metric, right. I
actually used to work for Yahoo, on the corporate finance side not on the
investment side. I suspect all these metrics come straight form the stock
exchanges, right.

I could forgo the 6-month metric if it doesn’t exist, but would like to pick
it up if there is a way. In any event, how would I code the macro above to
do what I described?


Thanks,
Ryan--

 

[Matthew Herbert]

Ryan,

The web query will work for you; however, I recommend NEVER using .WebTables
(unless the tables are named, which I've seen once via Microsoft's MSN Money
website). The reason for not using .WebTables is this, the content of the
webpage is continually changing due to webpage updates and changing
advertisements. As a result, there is no way of knowing that table '24' is
the "Trailing Returns" table. So, use .WebSelectionType = xlEntirePage and
search for the data on the worksheet.

I've placed some illustrative code below that adds a temporary worksheet and
then deletes that worksheet when the macro is done. I'm simply printing the
values to the Immediate Window (View | Immediate Window), but the output can
easily be placed on the desired worksheet in the desired cell.

As for the 6-month number, you could pull in the pricing data and manually
do the calculations. Though this is a bit more work, the XMLHTTP object
makes pulling the data from the website rather fast and easy (e.g. Set
objXMLHTTP = CreateObject("Microsoft.XMLHTTP"), or set the reference).
Alternatively, you could probably set the webpage URL to return only the
pricing from specific dates and then get that data for the calculation. For
example, you can see the following website for basic concepts (which can be
adapted for other uses):
http://www.etraderzone.com/free-scripts/47-historical-quotes-yahoo.html.
Anyhow, I'm simply trying to throw out some ideas.

Let me know if this is helpful. The code has hardly been tested, so be sure
to audit it. (Also, I'm using some poor assumptions with .Find, so be sure
to look up the Find Method and read about it to ensure the appropriate
settings. I'm only searching for the first instance of 1-Month, though there
are multiple instances).

Best,

Matthew Herbert

Sub Import()
Dim strURL As String
Dim rngCell As Range
Dim strText As String
Dim Wks As Worksheet
Dim rngFind As Range
Dim varArr As Variant
Dim intCnt As Integer
Dim intCol As Integer
Const c_strURL As String = "http://finance.yahoo.com/q/pm?s="

Set Wks = ThisWorkbook.Worksheets.Add
varArr = Array("1-Month", "3-Month")

For Each rngCell In Sheets("Sheet1").Range("A3:A7")
strURL = "URL;" & c_strURL & rngCell.Value
Wks.Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:=strURL _
, Destination:=Wks.Range("B3"))
.WebFormatting = xlWebFormattingNone
.WebSelectionType = xlEntirePage
'.WebTables = "24"
.Refresh BackgroundQuery:=False
End With
With Wks
intCol = 1
For intCnt = LBound(varArr) To UBound(varArr)
Set rngFind = .Cells.Find(varArr(intCnt))
If rngFind Is Nothing Then
Debug.Print varArr(intCnt) & " not found for " & rngCell.Value
Else
Do Until rngFind.Offset(0, intCol).Value <> vbNullString
intCol = intCol + 1
Loop
Debug.Print varArr(intCnt) & " for " & rngCell.Value & ":" &
rngFind.Offset(0, intCol).Value
End If
Next intCnt
End With
Next rngCell
Application.DisplayAlerts = False
Wks.Delete
Application.DisplayAlerts = True
End Sub

 

[RyGuy]

Thanks Matt! I tried the macro and no data was returned. Did that actually
work for you? I know the macro creates a new sheet, and then deletes the
sheet, but nothing was populated in my Sheet1. I stepped through the code; I
don't see it doing anything on my 'Sheet1'. Am I doing something wrong?

Thanks,
Ryan--

 

[Matthew Herbert]

Ryan,

Are you connected to the Internet? Do you have any settings that might
prevent a data connection? If you record a macro to get external data, is
data returned to the spreadsheet?

The macro worked fine for me.

Best,

Matt

 

[ryguy7272]

Thanks for getting back to me, Matthew. I'm still getting the same result.
I downed my computer and rebooted; same thing. I'll try on another system
tonight. Maybe I'll have more luck with that.

Thanks!
Ryan--

 

[ron]

Ryan...Give the following code a try. I didn't see 6-month returns,
but it should grab the rest of what you want...Ron


Dim fund_array(5) As String

Sub Fund_Returns()
fundarray = Array("RPBAX", "TRBCX", "PRWCX", "PRCOX", "PRDMX")

For i = 0 To 4
' get the source code
my_url = "http://finance.yahoo.com/q/pm?s=" & fundarray(i)
Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "GET", my_url, False
my_obj.send
my_var = my_obj.responsetext
Set my_obj = Nothing

' determine the yields
pos_1 = InStr(1, my_var, "1-Month", vbTextCompare)
pos_2 = InStr(pos_1, my_var, "right", vbTextCompare)
pos_3 = InStr(pos_2, my_var, ">", vbTextCompare)
pos_4 = InStr(pos_3, my_var, "<", vbTextCompare)
one_month_yld = Mid(my_var, 1 + pos_3, pos_4 - (1 + pos_3))

pos_1 = InStr(1, my_var, "3-Month", vbTextCompare)
pos_2 = InStr(pos_1, my_var, "right", vbTextCompare)
pos_3 = InStr(pos_2, my_var, ">", vbTextCompare)
pos_4 = InStr(pos_3, my_var, "<", vbTextCompare)
three_month_yld = Mid(my_var, 1 + pos_3, pos_4 - (1 + pos_3))

pos_1 = InStr(1, my_var, "1-Year", vbTextCompare)
pos_11 = InStr(1 + pos_1, my_var, "1-Year", vbTextCompare)
pos_2 = InStr(pos_11, my_var, "right", vbTextCompare)
pos_3 = InStr(pos_2, my_var, ">", vbTextCompare)
pos_4 = InStr(pos_3, my_var, "<", vbTextCompare)
one_year_yld = Mid(my_var, 1 + pos_3, pos_4 - (1 + pos_3))

' put the data where you want
Range("A" & 1 + i) = fundarray(i)
Range("B" & 1 + i) = one_month_yld
Range("C" & 1 + i) = three_month_yld
Range("D" & 1 + i) = one_year_yld
Next
End Sub

 

[Frank K]

When I ran the macro I too saw no updating until I looked in the Immediate
window. The values are posted there instead of on your Sheet1.

 

[ryguy7272]

Finally had a chance to revisit this; that macro is pretty sweet! Thanks so
much Ron!! SO, there's no way to get the 6-month returns because it's not on
the site, right. I looked, but didn't see it anywhere. I looked on
www.google.com/finance and didn't see any 6-month return metrics there
either. It seems like it's not a popular thing, I guess. Does anyone know?

Thanks again Ron!

 

[Matthew Herbert]

Ryan,

You can get the data yourself and do the calculations for any return for any
period. As I mentioned before, and as Ron pointed out, the XML object can be
used to query a server for information. Pricing data works equally as well
as the pre-canned pages that Yahoo! provides; however, the pricing data comes
in a nice delimited format rather than the HTML tags seen via the pre-canned
pages. See an example below.

Again, the results are printed to the Immediate Window (as were the results
from the web query I provided earlier). You can open the Immediate Window
via Ctrl+g or View|Immediate Window. The Immediate Window can also be
resized and moved. The Debug.Print statement prints items to the Immediate
Window.

Best,

Matt

Sub GetData()
Dim strRes As String
Dim varArr As Variant

'there are ways to build the URL to return specified data ranges to avoid
' having to wait for querying an entire stock history as opposed to
' the prices over the last year or two years
strRes = GetXMLHTTP("http://ichart.finance.yahoo.com/table.csv?s=INTC")
Debug.Print strRes

'split the results into an array
varArr = Split(strRes, vbLf)

'do something with the array, i.e. parse the data and loop through to
' perform your calculations

End Sub

Function GetXMLHTTP(strURL As String) As String

Dim objXMLHTTP As Object
Dim strText As String
Dim lngPos As Long

If strURL = "" Then
GetXMLHTTP = ""
Exit Function
End If

Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")

'should probably add an On Error statement to catch
' an instance when a bad strURL might throw an error
' in the .Open method.
With objXMLHTTP
.Open "GET", strURL, False
.Send
strText = .responseText
End With

If objXMLHTTP.statusText = "OK" Then
GetXMLHTTP = strText
Else
GetXMLHTTP = ""
End If

End Function