Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual funds
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''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual funds
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''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual fund
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''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual fund
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''. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual fund
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''. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual funds
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual fund
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''. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual fund
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! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ron" wrote: 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import 1, 3, 6, and 12 month returns for a list of mutual fund
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 "ryguy7272" wrote: 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! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ron" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Future Value of Mutual Funds | Excel Discussion (Misc queries) | |||
Investing,Mutual Funds Formula? | Excel Worksheet Functions | |||
Function that returns the month name | Excel Worksheet Functions | |||
where to find excel template for ROI stocks/mutual funds | Excel Discussion (Misc queries) | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) |