9 Messages
Digest #3807
Messages
Fri Sep 30, 2016 7:23 am (PDT) . Posted by:
joelfp2000
Good morning Randy,
I was wondering if I could get your help on a formula. I can use the following to obtain the MS return and MS risk but I can't figure out how to get the MS rating on an ETF.
=RCHGetTableCell("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$A1,1,"3-year",">",">",">")
(cell A1 is HYG or EMM, etc.)
Any suggestions on what I am doing wrong or any other formula that I can use?
Thanks in advance for your help with this. I appreciate all that you do.
Joel
I was wondering if I could get your help on a formula. I can use the following to obtain the MS return and MS risk but I can't figure out how to get the MS rating on an ETF.
=RCHGetTableCell("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$A1,1,
(cell A1 is HYG or EMM, etc.)
Any suggestions on what I am doing wrong or any other formula that I can use?
Thanks in advance for your help with this. I appreciate all that you do.
Joel
Fri Sep 30, 2016 8:00 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The problem is that the rating is an image, as HTML code. RCHGetTableCell()
strips out HTML code before trying to convert.
Try this for the top rating:
=smfConvertData(smfStrExtr(smfGetTagContent("
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$C5,"td",2,">Morningstar
Rating"),"/im/","stars.gif"))
...or something like this for the "Relative to Category" ratings:
=smfConvertData(smfStrExtr(smfGetTagContent("
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&C6,"td",3,">Morningstar
Risk",">3-Year"),"/im/","stars.gif"))
On Fri, Sep 30, 2016 at 7:23 AM, joel.andrews@l
...wrote:
>
> I was wondering if I could get your help on a formula. I can use the
> following to obtain the MS return and MS risk but I can't figure out how to
> get the MS rating on an ETF.
>
> =RCHGetTableCell("http://quicktake.morningstar.com/
> fundnet/printreport.aspx?symbol="&$A1,1,"3-year",">",">",">")
>
> (cell A1 is HYG or EMM, etc.)
>
> Any suggestions on what I am doing wrong or any other formula that I can
> use?
>
>
>
strips out HTML code before trying to convert.
Try this for the top rating:
=smfConvertData(smfStrExtr(smfGetTagContent("
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$C5,"td",2,">Morningstar
Rating"),"/im/","stars.gif"))
...or something like this for the "Relative to Category" ratings:
=smfConvertData(smfStrExtr(smfGetTagContent("
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&C6,"td",3,">Morningstar
Risk",">3-Year"),"/im/","stars.gif"))
On Fri, Sep 30, 2016 at 7:23 AM, joel.andrews@l
...wrote:
>
> I was wondering if I could get your help on a formula. I can use the
> following to obtain the MS return and MS risk but I can't figure out how to
> get the MS rating on an ETF.
>
> =RCHGetTableCell("http://quicktake.morningstar.com/
> fundnet/printreport
>
> (cell A1 is HYG or EMM, etc.)
>
> Any suggestions on what I am doing wrong or any other formula that I can
> use?
>
>
>
Fri Sep 30, 2016 9:52 pm (PDT) . Posted by:
joelfp2000
Randy, thanks so much for your help!
The first formula you provided for the rating appears blank on my spreadsheet. If I change the line item "td",3, to a 4 it pulls a number but I'm not sure if it is pulling the correct Morningstar rating. Do I possibly have a setting wrong in my excel and/or would you please verify. Thanks.
=smfConvertData(smfstrExtr(smfGetTagContent("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&A1,"td",3,">Morningstar Rating"),"/im/","stars.gif"))
Joel
The first formula you provided for the rating appears blank on my spreadsheet. If I change the line item "td",3, to a 4 it pulls a number but I'm not sure if it is pulling the correct Morningstar rating. Do I possibly have a setting wrong in my excel and/or would you please verify. Thanks.
=smfConvertData(smfstrExtr(smfGetTagContent("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&A1,"
Joel
Fri Sep 30, 2016 11:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The formula I sent, and the one you're citing below, both work fine for me.
On SPY, VFINX, and EMM. I do get nothing back for HYG, but if I look at the
print page, it doesn't have a Morningstar Rating on it.
On Fri, Sep 30, 2016 at 9:51 PM, joel.andrews@
...wrote:
>
> The first formula you provided for the rating appears blank on my
> spreadsheet. If I change the line item "td",3, to a 4 it pulls a number but
> I'm not sure if it is pulling the correct Morningstar rating. Do I
> possibly have a setting wrong in my excel and/or would you please verify.
> Thanks.
>
> =smfConvertData(smfstrExtr(smfGetTagContent("http://
> quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&A1,"td",3,">Morningstar
> Rating"),"/im/","stars.gif"))
> <http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=>
>
>
>
On SPY, VFINX, and EMM. I do get nothing back for HYG, but if I look at the
print page, it doesn't have a Morningstar Rating on it.
On Fri, Sep 30, 2016 at 9:51 PM, joel.andrews@
...wrote:
>
> The first formula you provided for the rating appears blank on my
> spreadsheet. If I change the line item "td",3, to a 4 it pulls a number but
> I'm not sure if it is pulling the correct Morningstar rating. Do I
> possibly have a setting wrong in my excel and/or would you please verify.
> Thanks.
>
> =smfConvertData(smfstrExtr(smfGetTagContent("http://
> quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&A1,"td",3,">Morningstar
> Rating"),"/im/","stars.gif"))
> <http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=>
>
>
>
Fri Sep 30, 2016 11:37 pm (PDT) . Posted by:
peter.oliver
Hi,
I'm not sure if anyone already reported this problem (I could not find it searching):
- I use RCHGetYahooHistory to get the "adjusted to dividends and split close price" of the day of purchase of all stocks in my portfolio as well the close price of the same day from Yahoo. The close price is OK with the value obtained from the stock in the Yahoo website however the adjusted close price is completely off from the corresponding value in the Yahoo website. It never gets the right value even days after the search so it does not seem a sync server problem.
As an example I'm looking at the close and adjusted close prices for ABBV at 02 Jan, 2013. I also tried to use RCHGetWebData to access the value for no use. The last reference string on the bottom of the page is actually not at the bottom of the page initially; the page goes expanding. The date showing close to:
"*Close price adjusted for dividends and splits." is 11 May, 2016.
RCHGetYahooHistory(A4,YEAR(BR4),MONTH(BR4),DAY(BR4),YEAR(BR4),MONTH(BR4),DAY(BR4),,$BO$1,0,0)
A4="ABBV"
BR4=02 Jan, 2015
$BO$1="C" or "A"
close price = 35.12 correct
adjusted price = 30.78 from the formula, correct value on Yahoo website for ABBV on 02 Jan, 2013= 28.13
Any suggestion on how to get the right data, please?
Regards,
Peter.
I'm not sure if anyone already reported this problem (I could not find it searching):
- I use RCHGetYahooHistory to get the "adjusted to dividends and split close price" of the day of purchase of all stocks in my portfolio as well the close price of the same day from Yahoo. The close price is OK with the value obtained from the stock in the Yahoo website however the adjusted close price is completely off from the corresponding value in the Yahoo website. It never gets the right value even days after the search so it does not seem a sync server problem.
As an example I'm looking at the close and adjusted close prices for ABBV at 02 Jan, 2013. I also tried to use RCHGetWebData to access the value for no use. The last reference string on the bottom of the page is actually not at the bottom of the page initially; the page goes expanding. The date showing close to:
"*Close price adjusted for dividends and splits." is 11 May, 2016.
RCHGetYahooHistory(
A4="ABBV"
BR4=02 Jan, 2015
$BO$1="
close price = 35.12 correct
adjusted price = 30.78 from the formula, correct value on Yahoo website for ABBV on 02 Jan, 2013= 28.13
Any suggestion on how to get the right data, please?
Regards,
Peter.
Sat Oct 1, 2016 12:18 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The problem is that the CSV download file and what the web page displays
are not the same. This is what I see on the download file:
Date Open High Low Close Volume Adj Close
1/2/2013 34.919998 35.400002 34.099998 35.119999 13767900 30.776335
So the add-in is just returning what Yahoo is sending.
It looks to me like the CSV file is correct. It had six decimal places of
accuracy on adjusted close. And if I look at even the most recent dividend
payment, the adjusted closing prices aren't even correct to two decimal
places. For example, displayed on the web page:
Date Open High Low Close Adj Close* Volume
13-Jul-16 0.57 Dividend
12-Jul-16 64.1 65.24 63.75 65.5 64.93 11,870,500
11-Jul-16 63.68 64.42 63.5 64.35 63.78 9,641,500
So, $64.93/$65.5 should be the ratio applied to all history after the $0.57
dividend is paid. That's 0.991297709923664.
But 0.991297709923664 * $64.35 is $63.7900076335878. But the above
displayed data shows $63.78.
$63.78 is NOT correct. The CSV file, however, shows $63.790007. Actually,
also not correct, because that sixth decimal place should be rounded up. :)
But a heck of a lot closer!
On Fri, Sep 30, 2016 at 11:37 PM, peter.oliver@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm not sure if anyone already reported this problem (I could not find it
> searching):
>
> - I use RCHGetYahooHistory to get the "adjusted to dividends and split
> close price" of the day of purchase of all stocks in my portfolio as well
> the close price of the same day from Yahoo. The close price is OK with the
> value obtained from the stock in the Yahoo website however the adjusted
> close price is completely off from the corresponding value in the Yahoo
> website. It never gets the right value even days after the search so it
> does not seem a sync server problem.
>
> As an example I'm looking at the close and adjusted close prices for ABBV
> at 02 Jan, 2013. I also tried to use RCHGetWebData to access the value for
> no use. The last reference string on the bottom of the page is actually not
> at the bottom of the page initially; the page goes expanding. The date
> showing close to:
>
> "*Close price adjusted for dividends and splits." is 11 May, 2016.
>
> RCHGetYahooHistory(A4,YEAR(BR4),MONTH(BR4),DAY(BR4),YEAR(
> BR4),MONTH(BR4),DAY(BR4),,$BO$1,0,0)
>
> A4="ABBV"
>
> BR4=02 Jan, 2015
>
> $BO$1="C" or "A"
>
> close price = 35.12 correct
>
> adjusted price = 30.78 from the formula, correct value on Yahoo website
> for ABBV on 02 Jan, 2013= 28.13
>
> Any suggestion on how to get the right data, please?
>
are not the same. This is what I see on the download file:
Date Open High Low Close Volume Adj Close
1/2/2013 34.919998 35.400002 34.099998 35.119999 13767900 30.776335
So the add-in is just returning what Yahoo is sending.
It looks to me like the CSV file is correct. It had six decimal places of
accuracy on adjusted close. And if I look at even the most recent dividend
payment, the adjusted closing prices aren't even correct to two decimal
places. For example, displayed on the web page:
Date Open High Low Close Adj Close* Volume
13-Jul-16 0.57 Dividend
12-Jul-16 64.1 65.24 63.75 65.5 64.93 11,870,500
11-Jul-16 63.68 64.42 63.5 64.35 63.78 9,641,500
So, $64.93/$65.5 should be the ratio applied to all history after the $0.57
dividend is paid. That's 0.991297709923664.
But 0.991297709923664 * $64.35 is $63.7900076335878. But the above
displayed data shows $63.78.
$63.78 is NOT correct. The CSV file, however, shows $63.790007. Actually,
also not correct, because that sixth decimal place should be rounded up. :)
But a heck of a lot closer!
On Fri, Sep 30, 2016 at 11:37 PM, peter.oliver@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm not sure if anyone already reported this problem (I could not find it
> searching):
>
> - I use RCHGetYahooHistory to get the "adjusted to dividends and split
> close price" of the day of purchase of all stocks in my portfolio as well
> the close price of the same day from Yahoo. The close price is OK with the
> value obtained from the stock in the Yahoo website however the adjusted
> close price is completely off from the corresponding value in the Yahoo
> website. It never gets the right value even days after the search so it
> does not seem a sync server problem.
>
> As an example I'm looking at the close and adjusted close prices for ABBV
> at 02 Jan, 2013. I also tried to use RCHGetWebData to access the value for
> no use. The last reference string on the bottom of the page is actually not
> at the bottom of the page initially; the page goes expanding. The date
> showing close to:
>
> "*Close price adjusted for dividends and splits." is 11 May, 2016.
>
> RCHGetYahooHistory(
> BR4),MONTH(BR4)
>
> A4="ABBV"
>
> BR4=02 Jan, 2015
>
> $BO$1="
>
> close price = 35.12 correct
>
> adjusted price = 30.78 from the formula, correct value on Yahoo website
> for ABBV on 02 Jan, 2013= 28.13
>
> Any suggestion on how to get the right data, please?
>
Sat Oct 1, 2016 12:51 am (PDT) . Posted by:
jbr863
Hello,
I've noticed that when I try to run smfGetOptionStrikes using Yahoo after-hours it returns no information, but it works fine when the market's open. I used to use Google to overcome this but Google has stopped listing all available expiration dates for their option chains so Yahoo is now more comprehensive. Anyone know how to get Option Strikes after-hours?
Best,
Jonathan
I've noticed that when I try to run smfGetOptionStrikes using Yahoo after-hours it returns no information, but it works fine when the market's open. I used to use Google to overcome this but Google has stopped listing all available expiration dates for their option chains so Yahoo is now more comprehensive. Anyone know how to get Option Strikes after-hours?
Best,
Jonathan
Sat Oct 1, 2016 1:35 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Here's what I get right now for Yahoo, Google, and OptionsXPress, and it's
after hours right now:
MMM MMM MMM
2016-10-07 2016-10-07 2016-10-07
C C C
Y G OX
$157.50
$160.00
$162.50
$165.00
$167.50
$170.00
$170.00 $172.50
$175.00 $175.00
$177.50 $177.50
$180.00 $180.00
$182.50 $182.50
$185.00 $185.00
$187.50 $187.50
$190.00
$192.50
$195.00
When I go to Google Finance, it has no options quotes for 10/7, so blanks
would be the correct answer. I don't know why Yahoo is missing so many
strike prices, but I think it has the same set when the market is open.
On Sat, Oct 1, 2016 at 12:51 AM, jbr863@
...wrote:
> I've noticed that when I try to run smfGetOptionStrikes using Yahoo
> after-hours it returns no information, but it works fine when the market's
> open. I used to use Google to overcome this but Google has stopped listing
> all available expiration dates for their option chains so Yahoo is now more
> comprehensive. Anyone know how to get Option Strikes after-hours?
>
>
>
>
after hours right now:
MMM MMM MMM
2016-10-07 2016-10-07 2016-10-07
C C C
Y G OX
$157.50
$160.00
$162.50
$165.00
$167.50
$170.00
$170.00 $172.50
$175.00 $175.00
$177.50 $177.50
$180.00 $180.00
$182.50 $182.50
$185.00 $185.00
$187.50 $187.50
$190.00
$192.50
$195.00
When I go to Google Finance, it has no options quotes for 10/7, so blanks
would be the correct answer. I don't know why Yahoo is missing so many
strike prices, but I think it has the same set when the market is open.
On Sat, Oct 1, 2016 at 12:51 AM, jbr863@
...wrote:
> I've noticed that when I try to run smfGetOptionStrikes using Yahoo
> after-hours it returns no information, but it works fine when the market's
> open. I used to use Google to overcome this but Google has stopped listing
> all available expiration dates for their option chains so Yahoo is now more
> comprehensive. Anyone know how to get Option Strikes after-hours?
>
>
>
>
Sat Oct 1, 2016 1:46 am (PDT) . Posted by:
"Jonathan" jbr863
Odd. The specific stock I was testing is "UVE" @ "5/19/2017". I still get nothing for any of the sources. Is the problem related to the uneven number of put and call strikes available?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar