EXCEL Stock Market Functions Add-in Group

4 Messages

Digest #4483

Re: Wrong Name for ticker by nelsonmuntz1000

Morningstar Asset Allocation Canadian-based ETFs by nelsonmuntz1000

Re: Morningstar Asset Allocation Canadian-based ETFs by "Randy Harmelink" rharmelink

Re: Morningstar Asset Allocation Canadian-based ETFs by nelsonmuntz1000

Messages

1a

Yahoo Finance doesn't really care about Canadian stocks. This has been an ongoing issue with TD and other tickers. Lots of comments and requests to have this corrected on Yahoo's feedback pages (eg https://yahoo.uservoice.com/forums/566647-finance-ca/suggestions/36171187-td-to-fix-it-please https://yahoo.uservoice.com/forums/566647-finance-ca/suggestions/36171187-td-to-fix-it-please).The finance page normally has the correct data on a 15 minute delay but I have seen that messed up occasionally. For some reason the retrieved stock name does not match the correct name on the Yahoo finance page https://ca.finance.yahoo.com/quote/TD.TO?p=TD.TO&.tsrc=fin-srch https://ca.finance.yahoo.com/quote/TD.TO?p=TD.TO&.tsrc=fin-srch. If you need to retrieve the name just pull it from the US ticker "TD". I wouldn't count on Yahoo to fix this. Verizon Media (formerly OATH) which includes Yahoo, had a massive writedown and cutbacks last year and is unlikely to give Canadian stocks very high priority. They already gutted Canadian mutual fund data.

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

2a

Morningstar Asset Allocation Canadian-based ETFs

Tue Feb 12, 2019 1:33 pm (PST) . Posted by:

nelsonmuntz1000

Hi Randy.
I use RCHGetElementNumber() 4714,4720 and 4734-4769 for periodic asset allocation retrieval on ETFs. For the equity portion of US and Canadian ETFs this works perfectly. There is a problem, however, when retrieving fixed income values for Canadian ETFs. US ETFs work properly. The problem appears to be the way Morningstar describes fixed income in the table near the top of the page. US Fixed income and balanced ETFs describe fixed income as "Bond".

US Examples: http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=AOM®ion=USA&culture=en-CA http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=AOM®ion=USA&culture=en-CA, http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=ANGL®ion=USA&culture=en-CA http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=ANGL®ion=USA&culture=en-CA

Canadian ETFs have different descriptors in the table, Fixed income is called "Fixed Income", not "Bond". Eg http://quote.morningstar.ca/QuickTakes/etf/etf_PortfolioNew.aspx?t=VGRO®ion=can http://quote.morningstar.ca/QuickTakes/etf/etf_PortfolioNew.aspx?t=VGRO®ion=can, http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=CBO®ion=CAN&culture=en-CA http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=CBO®ion=CAN&culture=en-CA

Element 4720 doesn't pull the correct data for Canadian ETFs. The following formula will work =RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/etf/etf_PortfolioNew.aspx?t=VGRO®ion=can",1,"<tbody","<tr","<tr",">Fixed Income")/100 but is it possible to use an element number in it's place?

The add-in has been working great for many months now. I really appreciate this tool. Thanks in advance.

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

2b

Re: Morningstar Asset Allocation Canadian-based ETFs

Tue Feb 12, 2019 2:52 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Since it's a specialized need, I'd suggest creating your own element
definitions, in the smf-elements-20.txt file. See the smf-elements-info.txt
file for information on that. You can use the smf-elements-20.txt to
redefine elements defined in previously read files (i.e. #1 thru #19), or,
if you do create a new element, I'd suggest something above #19000.

For example, something like:

19007;Test Page Name;Test ElementName;=smfGetTagContent("
http://portfolios.morningstar.com/fund/summary?t=~~~~~®ion=can","td",1,">Asset
Allocation",">Fixed Income",,,1)

Then, this returns the value for VGRO:

=RCHGetElementNumber("VGRO",19007)

After adding that line to the smf-elements-20.text file, you'll need to run
the smfForceRecalculation macro to have the add-in reload all of the
element definitions from the external files.

On Tue, Feb 12, 2019 at 2:33 PM ccoupe300@... wrote:

> I use RCHGetElementNumber() 4714,4720 and 4734-4769 for periodic asset
> allocation retrieval on ETFs. For the equity portion of US and Canadian
> ETFs this works perfectly. There is a problem, however, when retrieving
> fixed income values for Canadian ETFs. US ETFs work properly. The problem
> appears to be the way Morningstar describes fixed income in the table near
> the top of the page. US Fixed income and balanced ETFs describe fixed
> income as "Bond".
>
> US Examples:
> http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=AOM®ion=USA&culture=en-CA
> ,
> http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=ANGL®ion=USA&culture=en-CA
>
>
> Canadian ETFs have different descriptors in the table, Fixed income is
> called "Fixed Income", not "Bond". Eg
> http://quote.morningstar.ca/QuickTakes/etf/etf_PortfolioNew.aspx?t=VGRO®ion=can
> ,
> http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=CBO®ion=CAN&culture=en-CA
>
>
> Element 4720 doesn't pull the correct data for Canadian ETFs. The
> following formula will work =RCHGetTableCell("
> http://quote.morningstar.ca/QuickTakes/etf/etf_PortfolioNew.aspx?t=
> VGRO®ion=can",1,"<tbody","<tr","<tr",">Fixed Income")/100 but is it
> possible to use an element number in it's place?
>
> The add-in has been working great for many months now. I really appreciate
> this tool. Thanks in advance.
>
>
>