12 Messages
Digest #3301
Messages
Mon Jan 19, 2015 7:21 am (PST) . Posted by:
djsnicholson
I have been successfully using the SMF Add-in for a number of years and I would like to thank Randy for his tireless and very patient efforts at answering questions and maintaining and updating the add-in over the years. I am sure that it did not start out to be such a widespread used global tool, but it is. It has saved hundreds of thousands of hours for the totality of users. Randy, you have made a difference. Thank you.
Now to the subject at hand; the SMF Add-in data retrieval of Morningstar performance data items started to return erratic results during the past few weeks. The results table below is a small snippet from a spreadsheet which retrieves some annual and quarterly data for 6 tickers, but it exemplifies the problems in other Morningstar spreadsheets as well. By the way this spreadsheet was working perfectly for the past few years.
As shown, the ETF ticker is "vti" which is in cell B42.
The second column with the "Period" heading is column "C". and the "Period" is typed into cell C42. The "Error" in C43 has as contents "=RCHGetElementNumber(B42,4661)"; C43 has "=RCHGetElementNumber(B42,4662)" and returns the correct data. Cell C44 appropriately has "=RCHGetElementNumber(B42,4663)" but strangely returns "NaN", etc etc
The third column heading "Total Return" is in cell D42.
"=RCHGetElementNumber(B42,4856)" is in D43 returns "Error"
"=RCHGetElementNumber(B42,4857)" is in D44 returns correct number 12.6%
"=RCHGetElermentNumber(B42,4858)" is in D45 returns wrong 0.0%
These types of erratic Morningstar data retrieval problems are occurring in other spreadsheets as well. Yes, I am logged in on Internet Explorer as a Morningstar Premium user and have restarted and rebooted numerous times. The SMF Add-in was last updated last year. The bottom line is that I am not sure what is going on. Would welcome any suggestions or comments and learning if any other users have similar issues. Thanks.
David
vti Period Total Return Historical Performance through
12-31-14 Error Error 12-14 12.6% NaN 0.0% NaN 0.0%
Now to the subject at hand; the SMF Add-in data retrieval of Morningstar performance data items started to return erratic results during the past few weeks. The results table below is a small snippet from a spreadsheet which retrieves some annual and quarterly data for 6 tickers, but it exemplifies the problems in other Morningstar spreadsheets as well. By the way this spreadsheet was working perfectly for the past few years.
As shown, the ETF ticker is "vti" which is in cell B42.
The second column with the "Period" heading is column "C"
The third column heading "Total Return" is in cell D42.
"=RCHGetElemen
"=RCHGetElemen
"=RCHGetElerme
These types of erratic Morningstar data retrieval problems are occurring in other spreadsheets as well. Yes, I am logged in on Internet Explorer as a Morningstar Premium user and have restarted and rebooted numerous times. The SMF Add-in was last updated last year. The bottom line is that I am not sure what is going on. Would welcome any suggestions or comments and learning if any other users have similar issues. Thanks.
David
vti Period Total Return Historical Performance through
12-31-14 Error Error 12-14 12.6% NaN 0.0% NaN 0.0%
Mon Jan 19, 2015 7:35 am (PST) . Posted by:
djsnicholson
Sorry. the cell numbers in "Period" column were offset. Here is a simplified corrected paragraph along with the original "Total Return" column cells.
====================
The second column with the "Period" heading is column "C". and the
"Period" is typed into cell C42.
"=RCHGetElementNumber(B42, 4661)" in C43 returns "Error"
"=RCHGetElementNumber(B42, 4662)" in C44 returns the correct data. "12-14"
"=RCHGetElementNumber(B42, 4663)" in C45 strangely returns "NaN"
etc etc
============================
The third column heading "Total Return" is in cell D42.
"=RCHGetElementNumber(B42,4856)" is in D43 returns "Error"
"=RCHGetElementNumber(B42,4857)" is in D44 returns correct number 12.6%
"=RCHGetElermentNumber(B42,4858)" is in D45 returns wrong 0.0%
==========================
David
============
The second column with the "Period" heading is column "C"
"Period" is typed into cell C42.
"=RCHGetElemen
"=RCHGetElemen
"=RCHGetElemen
etc etc
============
The third column heading "Total Return" is in cell D42.
"=RCHGetElemen
"=RCHGetElemen
"=RCHGetElerme
============
David
Mon Jan 19, 2015 7:47 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The problem is that MorningStar is not keeping their print reports up to
date. The data you're retrieving is coming from this web page:
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=VTI
...and a lot of data is not populated right now. The web pages that are
kept up to date are something like:
http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=VTI
On Mon, Jan 19, 2015 at 8:21 AM, djsn@concentric.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I have been successfully using the SMF Add-in for a number of years and
> I would like to thank Randy for his tireless and very patient efforts at
> answering questions and maintaining and updating the add-in over the years.
> I am sure that it did not start out to be such a widespread used global
> tool, but it is. It has saved hundreds of thousands of hours for the
> totality of users. Randy, you have made a difference. Thank you.
>
> Now to the subject at hand; the SMF Add-in data retrieval of Morningstar
> performance data items started to return erratic results during the past
> few weeks. The results table below is a small snippet from a spreadsheet
> which retrieves some annual and quarterly data for 6 tickers, but it
> exemplifies the problems in other Morningstar spreadsheets as well. By the
> way this spreadsheet was working perfectly for the past few years.
>
> As shown, the ETF ticker is "vti" which is in cell B42.
>
> The second column with the "Period" heading is column "C". and the
> "Period" is typed into cell C42. The "Error" in C43 has as contents
> "=RCHGetElementNumber(B42,4661)"; C43 has "=RCHGetElementNumber(B42,4662)"
> and returns the correct data. Cell C44 appropriately has
> "=RCHGetElementNumber(B42,4663)" but strangely returns "NaN", etc etc
>
> The third column heading "Total Return" is in cell D42.
>
> "=RCHGetElementNumber(B42,4856)" is in D43 returns "Error"
>
> "=RCHGetElementNumber(B42,4857)" is in D44 returns correct number 12.6%
>
> "=RCHGetElermentNumber(B42,4858)" is in D45 returns wrong 0.0%
>
> These types of erratic Morningstar data retrieval problems are occurring
> in other spreadsheets as well. Yes, I am logged in on Internet Explorer
> as a Morningstar Premium user and have restarted and rebooted numerous
> times. The SMF Add-in was last updated last year. The bottom line is that I
> am not sure what is going on. Would welcome any suggestions or comments and
> learning if any other users have similar issues. Thanks.
>
>
> David
>
>
>
> *vti* *Period* *Total Return*
> *Historical Performance through 12-31-14* Error Error 12-14 12.6% NaN
> 0.0% NaN 0.0%
>
date. The data you're retrieving is coming from this web page:
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=VTI
...and a lot of data is not populated right now. The web pages that are
kept up to date are something like:
http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=VTI
On Mon, Jan 19, 2015 at 8:21 AM, djsn@concentric.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I have been successfully using the SMF Add-in for a number of years and
> I would like to thank Randy for his tireless and very patient efforts at
> answering questions and maintaining and updating the add-in over the years.
> I am sure that it did not start out to be such a widespread used global
> tool, but it is. It has saved hundreds of thousands of hours for the
> totality of users. Randy, you have made a difference. Thank you.
>
> Now to the subject at hand; the SMF Add-in data retrieval of Morningstar
> performance data items started to return erratic results during the past
> few weeks. The results table below is a small snippet from a spreadsheet
> which retrieves some annual and quarterly data for 6 tickers, but it
> exemplifies the problems in other Morningstar spreadsheets as well. By the
> way this spreadsheet was working perfectly for the past few years.
>
> As shown, the ETF ticker is "vti" which is in cell B42.
>
> The second column with the "Period" heading is column "C"
> "Period" is typed into cell C42. The "Error" in C43 has as contents
> "=RCHGetElemen
> and returns the correct data. Cell C44 appropriately has
> "=RCHGetElemen
>
> The third column heading "Total Return" is in cell D42.
>
> "=RCHGetElemen
>
> "=RCHGetElemen
>
> "=RCHGetElerme
>
> These types of erratic Morningstar data retrieval problems are occurring
> in other spreadsheets as well. Yes, I am logged in on Internet Explorer
> as a Morningstar Premium user and have restarted and rebooted numerous
> times. The SMF Add-in was last updated last year. The bottom line is that I
> am not sure what is going on. Would welcome any suggestions or comments and
> learning if any other users have similar issues. Thanks.
>
>
> David
>
>
>
> *vti* *Period* *Total Return*
> *Historical Performance through 12-31-14* Error Error 12-14 12.6% NaN
> 0.0% NaN 0.0%
>
Mon Jan 19, 2015 8:06 am (PST) . Posted by:
"David Nicholson" djsnicholson
Randy, thanks. Very interesting! I had no idea that Morningstar would not
have a consistent internally integrated set of data tables. Maybe it is
just a year-end updating issue on their end!?? Are there alternate
RCHGetElementNumber calls to utilize the Morningstar tables which are more
likely to be updated? or should I utilize the RCHGetTableCell function to
try to extract each element manually? Also, are there any other Morningstar
tips like this written anywhere?
Thanks.
David
On Mon, Jan 19, 2015 at 10:47 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> The problem is that MorningStar is not keeping their print reports up to
> date. The data you're retrieving is coming from this web page:
>
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=VTI
>
> ...and a lot of data is not populated right now. The web pages that are
> kept up to date are something like:
>
>
> http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=VTI
>
> On Mon, Jan 19, 2015 at 8:21 AM, djsn@concentric.net [smf_addin] <
> smf_addin@yahoogroups.com> wrote:
>
>> I have been successfully using the SMF Add-in for a number of years and
>> I would like to thank Randy for his tireless and very patient efforts at
>> answering questions and maintaining and updating the add-in over the years.
>> I am sure that it did not start out to be such a widespread used global
>> tool, but it is. It has saved hundreds of thousands of hours for the
>> totality of users. Randy, you have made a difference. Thank you.
>>
>> Now to the subject at hand; the SMF Add-in data retrieval of Morningstar
>> performance data items started to return erratic results during the past
>> few weeks. The results table below is a small snippet from a spreadsheet
>> which retrieves some annual and quarterly data for 6 tickers, but it
>> exemplifies the problems in other Morningstar spreadsheets as well. By the
>> way this spreadsheet was working perfectly for the past few years.
>>
>> As shown, the ETF ticker is "vti" which is in cell B42.
>>
>> The second column with the "Period" heading is column "C". and the
>> "Period" is typed into cell C42. The "Error" in C43 has as contents
>> "=RCHGetElementNumber(B42,4661)"; C43 has "=RCHGetElementNumber(B42,4662)"
>> and returns the correct data. Cell C44 appropriately has
>> "=RCHGetElementNumber(B42,4663)" but strangely returns "NaN", etc etc
>>
>> The third column heading "Total Return" is in cell D42.
>>
>> "=RCHGetElementNumber(B42,4856)" is in D43 returns "Error"
>>
>> "=RCHGetElementNumber(B42,4857)" is in D44 returns correct number 12.6%
>>
>> "=RCHGetElermentNumber(B42,4858)" is in D45 returns wrong 0.0%
>>
>> These types of erratic Morningstar data retrieval problems are occurring
>> in other spreadsheets as well. Yes, I am logged in on Internet Explorer
>> as a Morningstar Premium user and have restarted and rebooted numerous
>> times. The SMF Add-in was last updated last year. The bottom line is that I
>> am not sure what is going on. Would welcome any suggestions or comments and
>> learning if any other users have similar issues. Thanks.
>>
>>
>> David
>>
>>
>>
>> *vti* *Period* *Total Return*
>> *Historical Performance through 12-31-14* Error Error 12-14 12.6% NaN
>> 0.0% NaN 0.0%
>>
>
>
>
have a consistent internally integrated set of data tables. Maybe it is
just a year-end updating issue on their end!?? Are there alternate
RCHGetElementNumber calls to utilize the Morningstar tables which are more
likely to be updated? or should I utilize the RCHGetTableCell function to
try to extract each element manually? Also, are there any other Morningstar
tips like this written anywhere?
Thanks.
David
On Mon, Jan 19, 2015 at 10:47 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> The problem is that MorningStar is not keeping their print reports up to
> date. The data you're retrieving is coming from this web page:
>
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=VTI
>
> ...and a lot of data is not populated right now. The web pages that are
> kept up to date are something like:
>
>
> http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=VTI
>
> On Mon, Jan 19, 2015 at 8:21 AM, djsn@concentric.net [smf_addin] <
> smf_addin@yahoogroups.com> wrote:
>
>> I have been successfully using the SMF Add-in for a number of years and
>> I would like to thank Randy for his tireless and very patient efforts at
>> answering questions and maintaining and updating the add-in over the years.
>> I am sure that it did not start out to be such a widespread used global
>> tool, but it is. It has saved hundreds of thousands of hours for the
>> totality of users. Randy, you have made a difference. Thank you.
>>
>> Now to the subject at hand; the SMF Add-in data retrieval of Morningstar
>> performance data items started to return erratic results during the past
>> few weeks. The results table below is a small snippet from a spreadsheet
>> which retrieves some annual and quarterly data for 6 tickers, but it
>> exemplifies the problems in other Morningstar spreadsheets as well. By the
>> way this spreadsheet was working perfectly for the past few years.
>>
>> As shown, the ETF ticker is "vti" which is in cell B42.
>>
>> The second column with the "Period" heading is column "C"
>> "Period" is typed into cell C42. The "Error" in C43 has as contents
>> "=RCHGetElemen
>> and returns the correct data. Cell C44 appropriately has
>> "=RCHGetElemen
>>
>> The third column heading "Total Return" is in cell D42.
>>
>> "=RCHGetElemen
>>
>> "=RCHGetElemen
>>
>> "=RCHGetElerme
>>
>> These types of erratic Morningstar data retrieval problems are occurring
>> in other spreadsheets as well. Yes, I am logged in on Internet Explorer
>> as a Morningstar Premium user and have restarted and rebooted numerous
>> times. The SMF Add-in was last updated last year. The bottom line is that I
>> am not sure what is going on. Would welcome any suggestions or comments and
>> learning if any other users have similar issues. Thanks.
>>
>>
>> David
>>
>>
>>
>> *vti* *Period* *Total Return*
>> *Historical Performance through 12-31-14* Error Error 12-14 12.6% NaN
>> 0.0% NaN 0.0%
>>
>
>
>
Mon Jan 19, 2015 8:47 am (PST) . Posted by:
"Randy Harmelink" rharmelink
It's been discussed numerous times on the group...
They revamped the website a year or so ago and have slowly been abandoning
the print files. The one for stocks is gone now. They also changed all of
their web pages to be dynamic, so that the add-in can't grab the data from
them (because it pulls data out of the delivered source code of a web
page). However, I discovered the underlying web pages, like the one I
cited, that they use to populate the dynamic web pages. The problem is that
there are literally hundreds of such web pages for each equity. For
example, for their "performance" web page:
performance.morningstar.com/funds/etf/total-returns.action?t=VTI®ion=usa&culture=en-US
...there are a number of static web pages used to populate that web page:
http://performance.morningstar.com/Performance/cef/growth-chart.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=10&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/standardized-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=d&disclfalse&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/cost-risk.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
...and the parameters can vary. For example, the annual returns page above:
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
...returns 10 years of data, but changing "y=10" to "y=20" returns 20 years
of data (where available):
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=20&ndec=2&align=m&annlz=true
At some point, I should change the element definitions, but I'm daunted by
the sheer number of web pages and elements. In the past, such changes have
been an easy change of the URL and that's it. I'm also hesitant to do so
because of the number of web pages that might be accessed in gathering
data, because of the 1000-webpage limit of the add-in. The big advantage of
the print report page is that all of the data was on a single webpage and
could be accessed quickly. If that changes to 10 or 20 or 50 web pages to
get the data for a single stock, it represents a lot of extra time that
needs to be spent retrieving all of those web pages.
At the moment, you can do something like:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
",11,">History";,"(Price)")
...which is about what I'd end up using as the element definition.
In any case, for total return type numbers, I'd recommend using the
smfPricesByDates() function and getting adjusted closing prices from Yahoo
to compute the total returns yourself. Even now, the page above only
appears to be updated at the end of each month? Although historical years'
performance shouldn't change. Just the most recent year (or TTM or YTD?).
On Mon, Jan 19, 2015 at 9:05 AM, David Nicholson djsn@...wrote:
>
> Randy, thanks. Very interesting! I had no idea that Morningstar would not
> have a consistent internally integrated set of data tables. Maybe it is
> just a year-end updating issue on their end!?? Are there alternate
> RCHGetElementNumber calls to utilize the Morningstar tables which are more
> likely to be updated? or should I utilize the RCHGetTableCell function to
> try to extract each element manually? Also, are there any other Morningstar
> tips like this written anywhere?
>
They revamped the website a year or so ago and have slowly been abandoning
the print files. The one for stocks is gone now. They also changed all of
their web pages to be dynamic, so that the add-in can't grab the data from
them (because it pulls data out of the delivered source code of a web
page). However, I discovered the underlying web pages, like the one I
cited, that they use to populate the dynamic web pages. The problem is that
there are literally hundreds of such web pages for each equity. For
example, for their "performance" web page:
performance.morningstar.com/funds/etf/total-returns.action?t=VTI®ion=usa&culture=en-US
...there are a number of static web pages used to populate that web page:
http://performance.morningstar.com/Performance/cef/growth-chart.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=10&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/standardized-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=d&disclfalse&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
http://performance.morningstar.com/Performance/cef/cost-risk.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
...and the parameters can vary. For example, the annual returns page above:
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
...returns 10 years of data, but changing "y=10" to "y=20" returns 20 years
of data (where available):
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=20&ndec=2&align=m&annlz=true
At some point, I should change the element definitions, but I'm daunted by
the sheer number of web pages and elements. In the past, such changes have
been an easy change of the URL and that's it. I'm also hesitant to do so
because of the number of web pages that might be accessed in gathering
data, because of the 1000-webpage limit of the add-in. The big advantage of
the print report page is that all of the data was on a single webpage and
could be accessed quickly. If that changes to 10 or 20 or 50 web pages to
get the data for a single stock, it represents a lot of extra time that
needs to be spent retrieving all of those web pages.
At the moment, you can do something like:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
",11,"
...which is about what I'd end up using as the element definition.
In any case, for total return type numbers, I'd recommend using the
smfPricesByDates(
to compute the total returns yourself. Even now, the page above only
appears to be updated at the end of each month? Although historical years'
performance shouldn'
On Mon, Jan 19, 2015 at 9:05 AM, David Nicholson djsn@...wrote:
>
> Randy, thanks. Very interesting! I had no idea that Morningstar would not
> have a consistent internally integrated set of data tables. Maybe it is
> just a year-end updating issue on their end!?? Are there alternate
> RCHGetElementNumber calls to utilize the Morningstar tables which are more
> likely to be updated? or should I utilize the RCHGetTableCell function to
> try to extract each element manually? Also, are there any other Morningstar
> tips like this written anywhere?
>
Mon Jan 19, 2015 11:31 am (PST) . Posted by:
"David Nicholson" djsnicholson
Randy,
Many thanks again for your wise counsel. I have resolved 90% of my
Morningstar problems using the calls to the =RCHGetTableCell function. It
worked perfectly for both the trailing returns and annual returns.
However when I applied the same technique to retrieve quarterly returns
from your "historical-returns.action" table, I keep getting "Error"
returns. Here is your link from your email which I can see has the correct
data. I just can not retrieve the quarterly data in my spreadsheet.
Here is your link:
http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
As before, the VTI ticker is in cell B42. Here is my cell function call:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/historical-returns.action?t=
"&$B42,1,"2014","Q4")
Thanks.
David
On Mon, Jan 19, 2015 at 11:47 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> It's been discussed numerous times on the group...
>
> They revamped the website a year or so ago and have slowly been abandoning
> the print files. The one for stocks is gone now. They also changed all of
> their web pages to be dynamic, so that the add-in can't grab the data from
> them (because it pulls data out of the delivered source code of a web
> page). However, I discovered the underlying web pages, like the one I
> cited, that they use to populate the dynamic web pages. The problem is that
> there are literally hundreds of such web pages for each equity. For
> example, for their "performance" web page:
>
>
> performance.morningstar.com/funds/etf/total-returns.action?t=VTI®ion=usa&culture=en-US
>
> ...there are a number of static web pages used to populate that web page:
>
>
> http://performance.morningstar.com/Performance/cef/growth-chart.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=10&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/standardized-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=d&disclfalse&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
>
> http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/cost-risk.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
> ...and the parameters can vary. For example, the annual returns page above:
>
>
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
>
> ...returns 10 years of data, but changing "y=10" to "y=20" returns 20
> years of data (where available):
>
>
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=20&ndec=2&align=m&annlz=true
>
> At some point, I should change the element definitions, but I'm daunted by
> the sheer number of web pages and elements. In the past, such changes have
> been an easy change of the URL and that's it. I'm also hesitant to do so
> because of the number of web pages that might be accessed in gathering
> data, because of the 1000-webpage limit of the add-in. The big advantage of
> the print report page is that all of the data was on a single webpage and
> could be accessed quickly. If that changes to 10 or 20 or 50 web pages to
> get the data for a single stock, it represents a lot of extra time that
> needs to be spent retrieving all of those web pages.
>
> At the moment, you can do something like:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
> ",11,">History";,"(Price)")
>
> ...which is about what I'd end up using as the element definition.
>
> In any case, for total return type numbers, I'd recommend using the
> smfPricesByDates() function and getting adjusted closing prices from Yahoo
> to compute the total returns yourself. Even now, the page above only
> appears to be updated at the end of each month? Although historical years'
> performance shouldn't change. Just the most recent year (or TTM or YTD?).
>
> On Mon, Jan 19, 2015 at 9:05 AM, David Nicholson djsn@...wrote:
>
>>
>> Randy, thanks. Very interesting! I had no idea that Morningstar would
>> not have a consistent internally integrated set of data tables. Maybe it
>> is just a year-end updating issue on their end!?? Are there alternate
>> RCHGetElementNumber calls to utilize the Morningstar tables which are more
>> likely to be updated? or should I utilize the RCHGetTableCell function to
>> try to extract each element manually? Also, are there any other Morningstar
>> tips like this written anywhere?
>>
>
>
>
Many thanks again for your wise counsel. I have resolved 90% of my
Morningstar problems using the calls to the =RCHGetTableCell function. It
worked perfectly for both the trailing returns and annual returns.
However when I applied the same technique to retrieve quarterly returns
from your "historical-returns.action" table, I keep getting "Error"
returns. Here is your link from your email which I can see has the correct
data. I just can not retrieve the quarterly data in my spreadsheet.
Here is your link:
http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
As before, the VTI ticker is in cell B42. Here is my cell function call:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/historical-returns.action?t=
"&$B42,1,"2014","Q4")
Thanks.
David
On Mon, Jan 19, 2015 at 11:47 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> It's been discussed numerous times on the group...
>
> They revamped the website a year or so ago and have slowly been abandoning
> the print files. The one for stocks is gone now. They also changed all of
> their web pages to be dynamic, so that the add-in can't grab the data from
> them (because it pulls data out of the delivered source code of a web
> page). However, I discovered the underlying web pages, like the one I
> cited, that they use to populate the dynamic web pages. The problem is that
> there are literally hundreds of such web pages for each equity. For
> example, for their "performance" web page:
>
>
> performance.morningstar.com/funds/etf/total-returns.action?t=VTI®ion=usa&culture=en-US
>
> ...there are a number of static web pages used to populate that web page:
>
>
> http://performance.morningstar.com/Performance/cef/growth-chart.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=10&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/standardized-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=d&disclfalse&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
>
> http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
>
> http://performance.morningstar.com/Performance/cef/cost-risk.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=11&ndec=2&ep=true&align=m&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
> ...and the parameters can vary. For example, the annual returns page above:
>
>
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
>
> ...returns 10 years of data, but changing "y=10" to "y=20" returns 20
> years of data (where available):
>
>
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=20&ndec=2&align=m&annlz=true
>
> At some point, I should change the element definitions, but I'm daunted by
> the sheer number of web pages and elements. In the past, such changes have
> been an easy change of the URL and that's it. I'm also hesitant to do so
> because of the number of web pages that might be accessed in gathering
> data, because of the 1000-webpage limit of the add-in. The big advantage of
> the print report page is that all of the data was on a single webpage and
> could be accessed quickly. If that changes to 10 or 20 or 50 web pages to
> get the data for a single stock, it represents a lot of extra time that
> needs to be spent retrieving all of those web pages.
>
> At the moment, you can do something like:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/performance-history.action?&t=ARCX:VTI&cur=&s=0P00001MK8&y=10&ndec=2&align=m&annlz=true
> ",11,"
>
> ...which is about what I'd end up using as the element definition.
>
> In any case, for total return type numbers, I'd recommend using the
> smfPricesByDates(
> to compute the total returns yourself. Even now, the page above only
> appears to be updated at the end of each month? Although historical years'
> performance shouldn'
>
> On Mon, Jan 19, 2015 at 9:05 AM, David Nicholson djsn@...wrote:
>
>>
>> Randy, thanks. Very interesting! I had no idea that Morningstar would
>> not have a consistent internally integrated set of data tables. Maybe it
>> is just a year-end updating issue on their end!?? Are there alternate
>> RCHGetElementNumber calls to utilize the Morningstar tables which are more
>> likely to be updated? or should I utilize the RCHGetTableCell function to
>> try to extract each element manually? Also, are there any other Morningstar
>> tips like this written anywhere?
>>
>
>
>
Mon Jan 19, 2015 12:22 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Your formula doesn't work because the URL you are creating brings up a
blank web page. You took off too many parameters. Try:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
"&$B26,
1,"2014","Q4")
Rather than hard-code the year, you can use the HTML "ID"s they've set up
for each line. For example:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
"&$B26,
1,"year0_q0")
The suffix on year increments by one for each additional year. The "y="
parameter on the URL allows you to specify the number of years (even up to
30 or 40?). The suffix on quarters varies from 0 to 3. But I think there
are issues there on a first year that is incomplete.
On Mon, Jan 19, 2015 at 12:30 PM, David Nicholson djsn@...wrote:
>
> Randy,
> Many thanks again for your wise counsel. I have resolved 90% of my
> Morningstar problems using the calls to the =RCHGetTableCell function. It
> worked perfectly for both the trailing returns and annual returns.
>
> However when I applied the same technique to retrieve quarterly returns
> from your "historical-returns.action" table, I keep getting "Error"
> returns. Here is your link from your email which I can see has the correct
> data. I just can not retrieve the quarterly data in my spreadsheet.
>
> Here is your link:
>
>
> http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
> As before, the VTI ticker is in cell B42. Here is my cell function call:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/historical-returns.action?t=
> "&$B42,1,"2014","Q4")
>
>
blank web page. You took off too many parameters. Try:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
"&$B26,
1,"2014","Q4")
Rather than hard-code the year, you can use the HTML "ID"s they've set up
for each line. For example:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
"&$B26,
1,"year0_q0")
The suffix on year increments by one for each additional year. The "y="
parameter on the URL allows you to specify the number of years (even up to
30 or 40?). The suffix on quarters varies from 0 to 3. But I think there
are issues there on a first year that is incomplete.
On Mon, Jan 19, 2015 at 12:30 PM, David Nicholson djsn@...wrote:
>
> Randy,
> Many thanks again for your wise counsel. I have resolved 90% of my
> Morningstar problems using the calls to the =RCHGetTableCell function. It
> worked perfectly for both the trailing returns and annual returns.
>
> However when I applied the same technique to retrieve quarterly returns
> from your "historical-returns.action" table, I keep getting "Error"
> returns. Here is your link from your email which I can see has the correct
> data. I just can not retrieve the quarterly data in my spreadsheet.
>
> Here is your link:
>
>
> http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>
> As before, the VTI ticker is in cell B42. Here is my cell function call:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/historical-returns.action?t=
> "&$B42,
>
>
Mon Jan 19, 2015 6:18 pm (PST) . Posted by:
"David Nicholson" djsnicholson
Randy,
Many thanks. My remaining Morningstar quarterly data retrieval issues were
resolved with the revised new call, and as suggested I used variable
parameters for the year and quarter to simplify the automation process.
Thanks again.
David
PS: Is there a way in which I could contribute to the SMF support effort?
As mentioned earlier, you and SMF have saved hundreds of thousands of hours
across the userbase including quite a few of my personal hours. I would
like to contribute $500 to the SMF cause as a small token of my
appreciation.
On Mon, Jan 19, 2015 at 3:22 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Your formula doesn't work because the URL you are creating brings up a
> blank web page. You took off too many parameters. Try:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
> "&$B26,
> 1,"2014","Q4")
>
> Rather than hard-code the year, you can use the HTML "ID"s they've set up
> for each line. For example:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
> "&$B26,
> 1,"year0_q0")
>
> The suffix on year increments by one for each additional year. The "y="
> parameter on the URL allows you to specify the number of years (even up to
> 30 or 40?). The suffix on quarters varies from 0 to 3. But I think there
> are issues there on a first year that is incomplete.
>
> On Mon, Jan 19, 2015 at 12:30 PM, David Nicholson djsn@...wrote:
>
>>
>> Randy,
>> Many thanks again for your wise counsel. I have resolved 90% of my
>> Morningstar problems using the calls to the =RCHGetTableCell function. It
>> worked perfectly for both the trailing returns and annual returns.
>>
>> However when I applied the same technique to retrieve quarterly returns
>> from your "historical-returns.action" table, I keep getting "Error"
>> returns. Here is your link from your email which I can see has the correct
>> data. I just can not retrieve the quarterly data in my spreadsheet.
>>
>> Here is your link:
>>
>>
>> http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>>
>> As before, the VTI ticker is in cell B42. Here is my cell function call:
>>
>> =RCHGetTableCell("
>> http://performance.morningstar.com/Performance/cef/historical-returns.action?t=
>> "&$B42,1,"2014","Q4")
>>
>>
>
>
Many thanks. My remaining Morningstar quarterly data retrieval issues were
resolved with the revised new call, and as suggested I used variable
parameters for the year and quarter to simplify the automation process.
Thanks again.
David
PS: Is there a way in which I could contribute to the SMF support effort?
As mentioned earlier, you and SMF have saved hundreds of thousands of hours
across the userbase including quite a few of my personal hours. I would
like to contribute $500 to the SMF cause as a small token of my
appreciation.
On Mon, Jan 19, 2015 at 3:22 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Your formula doesn't work because the URL you are creating brings up a
> blank web page. You took off too many parameters. Try:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
> "&$B26,
> 1,"2014","Q4")
>
> Rather than hard-code the year, you can use the HTML "ID"s they've set up
> for each line. For example:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/cef/historical-returns.action?y=5&freq=q&t=
> "&$B26,
> 1,"year0_q0")
>
> The suffix on year increments by one for each additional year. The "y="
> parameter on the URL allows you to specify the number of years (even up to
> 30 or 40?). The suffix on quarters varies from 0 to 3. But I think there
> are issues there on a first year that is incomplete.
>
> On Mon, Jan 19, 2015 at 12:30 PM, David Nicholson djsn@...wrote:
>
>>
>> Randy,
>> Many thanks again for your wise counsel. I have resolved 90% of my
>> Morningstar problems using the calls to the =RCHGetTableCell function. It
>> worked perfectly for both the trailing returns and annual returns.
>>
>> However when I applied the same technique to retrieve quarterly returns
>> from your "historical-returns.action" table, I keep getting "Error"
>> returns. Here is your link from your email which I can see has the correct
>> data. I just can not retrieve the quarterly data in my spreadsheet.
>>
>> Here is your link:
>>
>>
>> http://performance.morningstar.com/Performance/cef/historical-returns.action?&t=ARCX:VTI®ion=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&y=5&ndec=2&ep=true&freq=q&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=
>>
>> As before, the VTI ticker is in cell B42. Here is my cell function call:
>>
>> =RCHGetTableCell("
>> http://performance.morningstar.com/Performance/cef/historical-returns.action?t=
>> "&$B42,
>>
>>
>
>
Mon Jan 19, 2015 6:53 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Thanks for the generous offer. I always ask people to "pay it forward" and
donate to a worthwhile charitable cause. Then you get a tax deduction out
of it as well, for all that extra profit you're making. :)
On Mon, Jan 19, 2015 at 7:17 PM, David Nicholson djsn@...wrote:
>
> PS: Is there a way in which I could contribute to the SMF support effort?
> As mentioned earlier, you and SMF have saved hundreds of thousands of hours
> across the userbase including quite a few of my personal hours. I would
> like to contribute $500 to the SMF cause as a small token of my
> appreciation.
>
donate to a worthwhile charitable cause. Then you get a tax deduction out
of it as well, for all that extra profit you're making. :)
On Mon, Jan 19, 2015 at 7:17 PM, David Nicholson djsn@...wrote:
>
> PS: Is there a way in which I could contribute to the SMF support effort?
> As mentioned earlier, you and SMF have saved hundreds of thousands of hours
> across the userbase including quite a few of my personal hours. I would
> like to contribute $500 to the SMF cause as a small token of my
> appreciation.
>
Mon Jan 19, 2015 7:58 pm (PST) . Posted by:
"David Nicholson" djsnicholson
Randy,
Classy! Thanks. $500 is on its way to Junior Achievement.
David
On Mon, Jan 19, 2015 at 9:53 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Thanks for the generous offer. I always ask people to "pay it forward" and
> donate to a worthwhile charitable cause. Then you get a tax deduction out
> of it as well, for all that extra profit you're making. :)
>
> On Mon, Jan 19, 2015 at 7:17 PM, David Nicholson djsn@...wrote:
>
>>
>> PS: Is there a way in which I could contribute to the SMF support effort?
>> As mentioned earlier, you and SMF have saved hundreds of thousands of hours
>> across the userbase including quite a few of my personal hours. I would
>> like to contribute $500 to the SMF cause as a small token of my
>> appreciation.
>>
>
>
>
Classy! Thanks. $500 is on its way to Junior Achievement.
David
On Mon, Jan 19, 2015 at 9:53 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Thanks for the generous offer. I always ask people to "pay it forward" and
> donate to a worthwhile charitable cause. Then you get a tax deduction out
> of it as well, for all that extra profit you're making. :)
>
> On Mon, Jan 19, 2015 at 7:17 PM, David Nicholson djsn@...wrote:
>
>>
>> PS: Is there a way in which I could contribute to the SMF support effort?
>> As mentioned earlier, you and SMF have saved hundreds of thousands of hours
>> across the userbase including quite a few of my personal hours. I would
>> like to contribute $500 to the SMF cause as a small token of my
>> appreciation.
>>
>
>
>
Hello,
This email message is a notification to let you know that
a file has been uploaded to the Files area of the smf_addin
group.
File : /Uploads by forum members/10 year model - GuruFocus - All Types (2003).xls
Uploaded by : hamishthedenizen <dan-carroll@yahoo.com>
Description : Converted template model to Excel 2003...please LMK if any issues
You can access this file at the URL:
https://groups.yahoo.com/neo/groups/smf_addin/files/Uploads%20by%20forum%20members/10%20year%20model%20-%20GuruFocus%20-%20All%20Types%20%282003%29.xls
To learn more about file sharing for your group, please visit:
https://help.yahoo.com/kb/index?page=content&y=PROD_GRPS&locale=en_US&id=SLN15398
Regards,
hamishthedenizen <dan-carroll@yahoo.com>
Tue Jan 20, 2015 3:09 am (PST) . Posted by:
hamishthedenizen
Brad, I only just saw your issue and it looks like Randy has solved it for you (as always!).
I have saved the file as Excel 2003 and uploaded it for others who may want it in that format. I'm not sure what functionality is lost, but if you get a chance, please let me know if it works for you.
I have saved the file as Excel 2003 and uploaded it for others who may want it in that format. I'm not sure what functionality is lost, but if you get a chance, please let me know if it works for you.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar