EXCEL Stock Market Functions Add-in Group

3 New Messages

Digest #2858

Re: Morningstar "Ratings & Risk" data by racecar00

Stopped working after web query by "Jim Ranum" amt2100

Re: Stopped working after web query by "Randy Harmelink" rharmelink

Messages

1a

Re: Morningstar "Ratings & Risk" data

Thu Nov 14, 2013 2:34 pm (PST) . Posted by:

racecar00

Thank you, Randy.

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

The URL you are using *is* a dynamic web page, so you can't extract the data from there. By going over the JavaScript code, I've learned that MorningStar *does* keep the data on static pages, but then loads them onto the dynamic page. In this case, the data you want is on this page (assuming you want the 3-year beta):

http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK

Here's how to extract the five possible definitions of beta:

=RCHGetTableCell("http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK",
4,"Best-Fit Index","<tr","<tr")

=RCHGetTableCell("http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK",
4,"Standard Index","<tr","<tr")

=RCHGetTableCell("http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=5&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=5&t=AFK",
4,"Standard Index","<tr","<tr")

=RCHGetTableCell("http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=10&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=10&t=AFK",
4,"Standard Index","<tr","<tr")

=RCHGetTableCell("http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=15&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=15&t=AFK",
4,"Standard Index","<tr","<tr")

My current status on extraction of ETF data from MorningStar has been to identify these pages as potential sources of raw data (either from their web page or because it is loaded onto a dynamic web page):

http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=3&t=AFK
http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=5&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=5&t=AFK
http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=10&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=10&t=AFK
http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=15&t=AFK http://performance.morningstar.com/RatingRisk/fund/mpt-statistics.action?y=15&t=AFK
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=3&t=AFK http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=3&t=AFK
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=5&t=AFK http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=5&t=AFK
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=10&t=AFK http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=10&t=AFK
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=15&t=AFK http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=15&t=AFK
http://portfolios.morningstar.com/fund/summary?t=AFK http://portfolios.morningstar.com/fund/summary?t=AFK
http://performance.morningstar.com/Performance/cef/premium-discount.action?t=AFK http://performance.morningstar.com/Performance/cef/premium-discount.action?t=AFK
http://performance.morningstar.com/Performance/cef/cost-risk.action?t=AFK http://performance.morningstar.com/Performance/cef/cost-risk.action?t=AFK
http://performance.morningstar.com/Performance/cef/performance-history.action?t=AFK http://performance.morningstar.com/Performance/cef/performance-history.action?t=AFK
http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=AFK http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=AFK
http://performance.morningstar.com/Performance/cef/standardized-returns.action?t=AFK http://performance.morningstar.com/Performance/cef/standardized-returns.action?t=AFK
http://portfolios.morningstar.com/fund/holdings?t=AFK http://portfolios.morningstar.com/fund/holdings?t=AFK
http://portfolios.morningstar.com/fund/ajax/holdings_tab?&t=AFK http://portfolios.morningstar.com/fund/ajax/holdings_tab?&t=AFK
http://portfolios.morningstar.com/fund/ajax/holdings_performanceChart?&t=AFK http://portfolios.morningstar.com/fund/ajax/holdings_performanceChart?&t=AFK
http://investors.morningstar.com/ownership/shareholders-overview.html?t=AFK http://investors.morningstar.com/ownership/shareholders-overview.html?t=AFK
http://investors.morningstar.com/ownership/equityOS-fund.html?t=AFK http://investors.morningstar.com/ownership/equityOS-fund.html?t=AFK
http://investors.morningstar.com/ownership/equityOS-inst.html?t=AFK http://investors.morningstar.com/ownership/equityOS-inst.html?t=AFK
http://investors.morningstar.com/ownership/owner-activity.html?t=AFK http://investors.morningstar.com/ownership/owner-activity.html?t=AFK
http://investors.morningstar.com/ownership/shareholders-concentrated.html?t=AFK http://investors.morningstar.com/ownership/shareholders-concentrated.html?t=AFK
http://investors.morningstar.com/ownership/shareholders-buying.html?t=AFK http://investors.morningstar.com/ownership/shareholders-buying.html?t=AFK
http://investors.morningstar.com/ownership/shareholders-selling.html?t=AFK http://investors.morningstar.com/ownership/shareholders-selling.html?t=AFK

On Wed, Nov 13, 2013 at 1:05 PM, <agelhausen@... mailto:agelhausen@...> wrote:

I am trying to capture risk metrics for ETFs using Morningstar's data. When using the RCHGetTableCell function & Morningstar I prefer to use the printreport pages but Morningstar seems to be trending towards the dynamic pages more and more.

I am trying to capture risj metrics using the following formula and link:

=RCHGetTableCell("http://performance.morningstar.com/funds/etf/ratings-risk.action?t= http://performance.morningstar.com/funds/etf/ratings-risk.action?t="&$C8,1,"AFK", "Beta")

I am having trouble with the syntax for identifying row and column - in this example, the beta metric for the ticker 'AFK'. There are three rows of data depending on benchmark preference.

Can you help me solve the syntax for using row 1 for ticker AFK (cell reference willbe used), column 4 titled "Beta".

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

2a

Stopped working after web query

Thu Nov 14, 2013 8:21 pm (PST) . Posted by:

"Jim Ranum" amt2100

Hi Randy,

Well I tried to create an Excel Web Query as you mentioned yesterday to get
the Shiller PE Ratio off of Gurufocus and now my spreadsheet has stopped
working when I change the ticker.

I have too much stuff on here to just start over. So maybe you can help me
sort it out?

After I tried to create the Web Query, getting the data for the Shiller PE
cell never worked. I couldn't find where to delete the web Query and I ended
up shutting down excel and the computer for a few hours while away. Came
back and opened the file and it hangs up, not responding, with "trying to
contact server" in the lower right corner of excel. After hitting ESC a
couple times, I can get back to the spreadsheet, but as I mentioned,
changing tickers won't update the data like it did before I did this Web
Query thing.

Now when I open the Web Query button, nothing is there to delete. But
obviously it lives on.

Where do I go from here?

Thanks,

Jim

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2b

Re: Stopped working after web query

Thu Nov 14, 2013 9:48 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

You don't need to CREATE a web query. Just visit the site with the Web
Query dialog, log in, then cancel out. I've not seen the symptoms you
describe.

You could run some VBA code to delete anything that might exist for a query:

Sub RemoveQueryNames()
Dim nQuery As Name
With ThisWorkbook
For Each nQuery In Names
nQuery.Delete
Next nQuery
End With
End Sub

On Thu, Nov 14, 2013 at 9:21 PM, Jim Ranum <amt2100@gmail.com> wrote:

>
>
> Well I tried to create an Excel Web Query as you mentioned yesterday to
> get the Shiller PE Ratio off of Gurufocus and now my spreadsheet has
> stopped working when I change the ticker.
>
> I have too much stuff on here to just start over. So maybe you can help me
> sort it out?
>
>
>
> After I tried to create the Web Query, getting the data for the Shiller PE
> cell never worked. I couldn't find where to delete the web Query and I
> ended up shutting down excel and the computer for a few hours while away.
> Came back and opened the file and it hangs up, not responding, with "trying
> to contact server" in the lower right corner of excel. After hitting ESC a
> couple times, I can get back to the spreadsheet, but as I mentioned,
> changing tickers won't update the data like it did before I did this Web
> Query thing.
>
> Now when I open the Web Query button, nothing is there to delete. But
> obviously it lives on.
>
>
>
> Where do I go from here?
>