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.
--
Frank K


"ryguy7272" wrote:

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--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Matthew Herbert" wrote:

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

"RyGuy" wrote:

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" wrote:

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-scri...tes-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



"ryguy7272" wrote:

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 doesnt 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.
Thats probably not too hard to do, but Im not exactly sure how to
incorporate it into the URL. Also, I dont see the 6-month return anywhere
on the page. So, Im thinking Yahoo doesnt 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 doesnt 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--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.