10 Messages
Digest #3493
Messages
Wed Aug 19, 2015 6:11 am (PDT) . Posted by:
"P Dunbar" pdunbar1942
Thanks. I had noticed the problem when deleting or replacing formulas of cells with charts that the images remain. Attempting to remove the image only seems to remove points in the chart. For now, I force an error in the function's formula in order to remove the image.
Just a quick follow-up: RGB settings I'm familiar with require three values. Can you point me to the applicable color codes for the smfinCellChart() function?
Thanks again, Randy. As others have written, SMF is a great tool.
On Wednesday, August 19, 2015 4:05 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
On Tue, Aug 18, 2015 at 7:18 PM, pdunbar1942@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
I've just installed SMF Add-In and am trying to display sparkline charts. There are three issues I hope to get pointers on:
- The documentation for RCHGetYahooHistory lists four "Periods", day, week, month and dividend. I'd like to display an intraday chart in a cell, as Yahoo!Finance displays; is this possible?
Not with RCHGetYahooHistory(). Those four periods are the only choices Yahoo offers for the historical quotes CSV interface.
You'd need to find another source of the data that can feed the vector into the function.
- Querying stock issues trading less than one year, the function returns a #VALUE error, such as the below example. Is there a way to resolve this and have the chart display the most recently available data?
- =smfInCellChart(RCHGetYahooHistory("BLD",,,,,,,"d","A",0,,1,365,1))
You could do something like:
=smfInCellChart(RCHGetYahooHistory("BLD",,,,,,,"d","A",0,,1,COUNT(RCHGetYahooHistory("BLD",,,,,,,"d","A",0,,1,365,1)),1))
...but it would be doing two Internet retrievals.
- Minor query: Is there a way to change the default color of the charts?
The third parameter of the function is for color. The default color is 203. It's an RGB value.
The second parameter allowed "Line", "Bar", or "Slope", but I think I had an issue with "Bar" when a lot of points were used. The closest there is to documentation:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/2818
I'm also looking for documentation on the smfInCellChart function. I've tried running a web search of www.ogres-crypt.com/documentation for the function without finding it. Perhaps I searched the wrong site/path.
I was never happy with the smfInCellChart() function (or RCHCreateComment()), so I left it undocumented. The biggest issue was they both deal with things that don't update normally. If an old shape or image existed, it had to be cleared. That doesn't happen if the cell content is deleted. The shapes or comments are left behind.
#yiv1579732976 #yiv1579732976 -- #yiv1579732976ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1579732976 #yiv1579732976ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1579732976 #yiv1579732976ygrp-mkp #yiv1579732976hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1579732976 #yiv1579732976ygrp-mkp #yiv1579732976ads {margin-bottom:10px;}#yiv1579732976 #yiv1579732976ygrp-mkp .yiv1579732976ad {padding:0 0;}#yiv1579732976 #yiv1579732976ygrp-mkp .yiv1579732976ad p {margin:0;}#yiv1579732976 #yiv1579732976ygrp-mkp .yiv1579732976ad a {color:#0000ff;text-decoration:none;}#yiv1579732976 #yiv1579732976ygrp-sponsor #yiv1579732976ygrp-lc {font-family:Arial;}#yiv1579732976 #yiv1579732976ygrp-sponsor #yiv1579732976ygrp-lc #yiv1579732976hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1579732976 #yiv1579732976ygrp-sponsor #yiv1579732976ygrp-lc .yiv1579732976ad {margin-bottom:10px;padding:0 0;}#yiv1579732976 #yiv1579732976actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1579732976 #yiv1579732976activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1579732976 #yiv1579732976activity span {font-weight:700;}#yiv1579732976 #yiv1579732976activity span:first-child {text-transform:uppercase;}#yiv1579732976 #yiv1579732976activity span a {color:#5085b6;text-decoration:none;}#yiv1579732976 #yiv1579732976activity span span {color:#ff7900;}#yiv1579732976 #yiv1579732976activity span .yiv1579732976underline {text-decoration:underline;}#yiv1579732976 .yiv1579732976attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1579732976 .yiv1579732976attach div a {text-decoration:none;}#yiv1579732976 .yiv1579732976attach img {border:none;padding-right:5px;}#yiv1579732976 .yiv1579732976attach label {display:block;margin-bottom:5px;}#yiv1579732976 .yiv1579732976attach label a {text-decoration:none;}#yiv1579732976 blockquote {margin:0 0 0 4px;}#yiv1579732976 .yiv1579732976bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1579732976 .yiv1579732976bold a {text-decoration:none;}#yiv1579732976 dd.yiv1579732976last p a {font-family:Verdana;font-weight:700;}#yiv1579732976 dd.yiv1579732976last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1579732976 dd.yiv1579732976last p span.yiv1579732976yshortcuts {margin-right:0;}#yiv1579732976 div.yiv1579732976attach-table div div a {text-decoration:none;}#yiv1579732976 div.yiv1579732976attach-table {width:400px;}#yiv1579732976 div.yiv1579732976file-title a, #yiv1579732976 div.yiv1579732976file-title a:active, #yiv1579732976 div.yiv1579732976file-title a:hover, #yiv1579732976 div.yiv1579732976file-title a:visited {text-decoration:none;}#yiv1579732976 div.yiv1579732976photo-title a, #yiv1579732976 div.yiv1579732976photo-title a:active, #yiv1579732976 div.yiv1579732976photo-title a:hover, #yiv1579732976 div.yiv1579732976photo-title a:visited {text-decoration:none;}#yiv1579732976 div#yiv1579732976ygrp-mlmsg #yiv1579732976ygrp-msg p a span.yiv1579732976yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1579732976 .yiv1579732976green {color:#628c2a;}#yiv1579732976 .yiv1579732976MsoNormal {margin:0 0 0 0;}#yiv1579732976 o {font-size:0;}#yiv1579732976 #yiv1579732976photos div {float:left;width:72px;}#yiv1579732976 #yiv1579732976photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1579732976 #yiv1579732976photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1579732976 #yiv1579732976reco-category {font-size:77%;}#yiv1579732976 #yiv1579732976reco-desc {font-size:77%;}#yiv1579732976 .yiv1579732976replbq {margin:4px;}#yiv1579732976 #yiv1579732976ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1579732976 #yiv1579732976ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1579732976 #yiv1579732976ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1579732976 #yiv1579732976ygrp-mlmsg select, #yiv1579732976 input, #yiv1579732976 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1579732976 #yiv1579732976ygrp-mlmsg pre, #yiv1579732976 code {font:115% monospace;}#yiv1579732976 #yiv1579732976ygrp-mlmsg * {line-height:1.22em;}#yiv1579732976 #yiv1579732976ygrp-mlmsg #yiv1579732976logo {padding-bottom:10px;}#yiv1579732976 #yiv1579732976ygrp-msg p a {font-family:Verdana;}#yiv1579732976 #yiv1579732976ygrp-msg p#yiv1579732976attach-count span {color:#1E66AE;font-weight:700;}#yiv1579732976 #yiv1579732976ygrp-reco #yiv1579732976reco-head {color:#ff7900;font-weight:700;}#yiv1579732976 #yiv1579732976ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1579732976 #yiv1579732976ygrp-sponsor #yiv1579732976ov li a {font-size:130%;text-decoration:none;}#yiv1579732976 #yiv1579732976ygrp-sponsor #yiv1579732976ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1579732976 #yiv1579732976ygrp-sponsor #yiv1579732976ov ul {margin:0;padding:0 0 0 8px;}#yiv1579732976 #yiv1579732976ygrp-text {font-family:Georgia;}#yiv1579732976 #yiv1579732976ygrp-text p {margin:0 0 1em 0;}#yiv1579732976 #yiv1579732976ygrp-text tt {font-size:120%;}#yiv1579732976 #yiv1579732976ygrp-vital ul li:last-child {border-right:none !important;}#yiv1579732976
Just a quick follow-up: RGB settings I'm familiar with require three values. Can you point me to the applicable color codes for the smfinCellChart() function?
Thanks again, Randy. As others have written, SMF is a great tool.
On Wednesday, August 19, 2015 4:05 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
On Tue, Aug 18, 2015 at 7:18 PM, pdunbar1942@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
I've just installed SMF Add-In and am trying to display sparkline charts. There are three issues I hope to get pointers on:
- The documentation for RCHGetYahooHistory lists four "Periods", day, week, month and dividend. I'd like to display an intraday chart in a cell, as Yahoo!Finance displays; is this possible?
Not with RCHGetYahooHistory(). Those four periods are the only choices Yahoo offers for the historical quotes CSV interface.
You'd need to find another source of the data that can feed the vector into the function.
- Querying stock issues trading less than one year, the function returns a #VALUE error, such as the below example. Is there a way to resolve this and have the chart display the most recently available data?
- =smfInCellChart(RCHGetYahooHistory("BLD",,,,,,,"d","A",0,,1,365,1))
You could do something like:
=smfInCellChart(RCHGetYahooHistory("BLD",,,,,,,"d","A",0,,1,COUNT(RCHGetYahooHistory("BLD",,,,,,,"d","A",0,,1,365,1)),1))
...but it would be doing two Internet retrievals.
- Minor query: Is there a way to change the default color of the charts?
The third parameter of the function is for color. The default color is 203. It's an RGB value.
The second parameter allowed "Line", "Bar", or "Slope", but I think I had an issue with "Bar" when a lot of points were used. The closest there is to documentation:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/2818
I'm also looking for documentation on the smfInCellChart function. I've tried running a web search of www.ogres-crypt.
I was never happy with the smfInCellChart(
#yiv1579732976 #yiv1579732976 -- #yiv1579732976ygrp-
Wed Aug 19, 2015 8:40 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Wed, Aug 19, 2015 at 6:11 AM, P Dunbar pdunbar1942@...wrote:
>
> Thanks. I had noticed the problem when deleting or replacing formulas of
> cells with charts that the images remain. Attempting to remove the image
> only seems to remove points in the chart. For now, I force an error in the
> function's formula in order to remove the image.
>
The shapes should remain if you delete the formula. But if you change the
formula, it should delete all shapes within the cell.
Just a quick follow-up: RGB settings I'm familiar with require three
> values. Can you point me to the applicable color codes for the
> smfinCellChart() function?
>
I use the Hex2Dec() function, but it appears to have red and blue hex
values reversed? So, something like:
Hex2Dec("FFFFFF")
Hex2Dec("0000FF")
>
> Thanks. I had noticed the problem when deleting or replacing formulas of
> cells with charts that the images remain. Attempting to remove the image
> only seems to remove points in the chart. For now, I force an error in the
> function'
>
The shapes should remain if you delete the formula. But if you change the
formula, it should delete all shapes within the cell.
Just a quick follow-up: RGB settings I'm familiar with require three
> values. Can you point me to the applicable color codes for the
> smfinCellChart(
>
I use the Hex2Dec() function, but it appears to have red and blue hex
values reversed? So, something like:
Hex2Dec("
Hex2Dec("
Hi Randy,
I have been using the Data Elements below to compare ETFs, CEFs, etc.
:
RETURN: 4977
STD DEVIATION: 4671
SHARPE: 4673
They all come from MorningStar and do work for ETFs.
Here is my question. Although MorningStar shows those data elements in a seemingly identical web page, when the target is a CEF, those Data Elements display as ZERO or ERROR.
Is there a way to fix this? Or is there another way to capture that info for CEFs?
Thanks
Armando
I have been using the Data Elements below to compare ETFs, CEFs, etc.
:
RETURN: 4977
STD DEVIATION: 4671
SHARPE: 4673
They all come from MorningStar and do work for ETFs.
Here is my question. Although MorningStar shows those data elements in a seemingly identical web page, when the target is a CEF, those Data Elements display as ZERO or ERROR.
Is there a way to fix this? Or is there another way to capture that info for CEFs?
Thanks
Armando
Wed Aug 19, 2015 8:57 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Actually, 4977 is from a Yahoo web page, but I believe MorningStar is the
ultimate source of the data.
I have no idea what a sample CEF ticker symbol would be, so I have no way
to check anything for them.
But those other two elements are coming from a print page that I don't
think MorningStar keeps fully populated. It has since been replaced with a
dynamically generated web page that gets source data from such web pages as:
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=3&t=VTI
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=5&t=VTI
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=10&t=VTI
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=15&t=VTI
http://performance.morningstar.com/Performance/cef/performance-history.action?t=VTI
On Wed, Aug 19, 2015 at 7:35 AM, armando@... wrote:
> I have been using the Data Elements below to compare ETFs, CEFs, etc.
>
> RETURN: 4977
>
> STD DEVIATION: 4671
>
> SHARPE: 4673
>
> They all come from MorningStar and do work for ETFs.
>
> Here is my question. Although MorningStar shows those data elements in a
> seemingly identical web page, when the target is a CEF, those Data Elements
> display as ZERO or ERROR.
>
> Is there a way to fix this? Or is there another way to capture that info
> for CEFs?
>
>
ultimate source of the data.
I have no idea what a sample CEF ticker symbol would be, so I have no way
to check anything for them.
But those other two elements are coming from a print page that I don't
think MorningStar keeps fully populated. It has since been replaced with a
dynamically generated web page that gets source data from such web pages as:
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=3&t=VTI
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=5&t=VTI
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=10&t=VTI
http://performance.morningstar.com/RatingRisk/fund/volatility-measurements.action?y=15&t=VTI
http://performance.morningstar.com/Performance/cef/performance-history.action?t=VTI
On Wed, Aug 19, 2015 at 7:35 AM, armando@... wrote:
> I have been using the Data Elements below to compare ETFs, CEFs, etc.
>
> RETURN: 4977
>
> STD DEVIATION: 4671
>
> SHARPE: 4673
>
> They all come from MorningStar and do work for ETFs.
>
> Here is my question. Although MorningStar shows those data elements in a
> seemingly identical web page, when the target is a CEF, those Data Elements
> display as ZERO or ERROR.
>
> Is there a way to fix this? Or is there another way to capture that info
> for CEFs?
>
>
Wed Aug 19, 2015 9:31 am (PDT) . Posted by:
nelsonmuntz1000
Randy, just want to let you know I really like this feature. I use this all the time in large complex spreadsheets, especially when adding new tickers. It saves a lot of time when updating. Great work.
Wed Aug 19, 2015 11:44 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Thanks go to Andrei Radulescu-Banu for that. I had nothing to do with it.
He was the one that offered up the whole menu idea. I added a few
additional items, but that was all his.
I didn't really see the point of it, since too many add-in functions need
the smfForceRecalculation process, to get a new web page before
recalculation is done. That can't really be compartmentalized.
On Wed, Aug 19, 2015 at 9:31 AM, ccoupe300@... wrote:
> Randy, just want to let you know I really like this feature. I use this
> all the time in large complex spreadsheets, especially when adding new
> tickers. It saves a lot of time when updating. Great work.
>
He was the one that offered up the whole menu idea. I added a few
additional items, but that was all his.
I didn't really see the point of it, since too many add-in functions need
the smfForceRecalculati
recalculation is done. That can't really be compartmentalized.
On Wed, Aug 19, 2015 at 9:31 AM, ccoupe300@..
> Randy, just want to let you know I really like this feature. I use this
> all the time in large complex spreadsheets, especially when adding new
> tickers. It saves a lot of time when updating. Great work.
>
Wed Aug 19, 2015 11:46 am (PDT) . Posted by:
jhfrazer@ymail.com
Hello Sir,
I have been a fan of your work and relied on it for some time. I have been unable to devise a way of reduucing the time it takes a larger version of this spreadsheet to calculate. I have included this slammer version so that you might enlighten me.
The Total Return formulas are accurate but needing nine separate downloads to provide the data for the Total Return Calculations is what is taking the time I believe.
Your assistance is greatly appreciated.
Cheers,
John
I have been a fan of your work and relied on it for some time. I have been unable to devise a way of reduucing the time it takes a larger version of this spreadsheet to calculate. I have included this slammer version so that you might enlighten me.
The Total Return formulas are accurate but needing nine separate downloads to provide the data for the Total Return Calculations is what is taking the time I believe.
Your assistance is greatly appreciated.
Cheers,
John
Attachment(s) from
1 of 1 File(s)
Wed Aug 19, 2015 1:41 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
===> 1. I don't believe your total return calculations are correct. Let me
give you an extreme example. Suppose you buy a stock for $10, and it goes
up to $20, then pays out a $10 dividend. Now, the current price is $10 and
the adjusted starting price is $5. The calculation in your worksheet of the
total return at that point is:
(End price - Adjusted Start price)/(Unadjusted Start Price) = ($10 - $5) /
($10) = 50%
But you're sitting there with a $10 dividend in your pocket, and you still
own a $10 stock, on a $10 investment. That's a 100% return. I always use
this as total return:
Adjusted end price / Adjusted start price - 1 = $10 / $5 - 1 = 100%
Keep in mind that the "adjusted" prices on Yahoo are just there to provide
context for price comparisons between dates. Yahoo could just as well just
put an adjustment ratio there instead of a price. Instead, we need to
compute that adjustment ratio ourselves, in order to apply it to O/H/L
prices.
===> 2. I have learned to NEVER base my date calculations in this type of
worksheet on the NOW() or TODAY() functions. Those are volatile formulas,
which change any formulas that use them into volatile formulas. That means
ANY change you make anywhere in EXCEL will cause all of those volatile
formulas to recalculate. Even just entering a value into a worksheet cell.
That's a recalculation nightmare. Instead, for "today's" date, I always use:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
...which just gets the last date that SPY was traded.
===> 3. Having said all that, all you need is the adjusted stock prices for
each of the four dates for each stock. That can be accomplished with a
single array-entered smfPricesByDates() function for each stock. Which
turns each set of eight RCHGetYahooQuotes() invocations into a single one
that is done by smfPricesByDates(), which then searches through the
returned data for the adjusted closing prices on the four specified dates.
Then you don't have to worry about the holidays either.
===> 4. You could combine all of the RCHGetYahooQuotes() functions into a
single array-entered one. In the sample worksheet you gave me, that turns
ten Internet accesses into a single one.
===> 5. In the end, you no longer need columns I thru P. Your formulas in
cells A20:D20 could be replaced by a single array-entered:
=F20/smfPricesByDates(H20,$A$19:$D$19)-1
On Wed, Aug 19, 2015 at 11:45 AM, jhfrazer@ymail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have been a fan of your work and relied on it for some time. I have
> been unable to devise a way of reduucing the time it takes a larger version
> of this spreadsheet to calculate. I have included this slammer version so
> that you might enlighten me.
>
> The Total Return formulas are accurate but needing nine separate downloads
> to provide the data for the Total Return Calculations is what is taking the
> time I believe.
>
> Your assistance is greatly appreciated.
>
give you an extreme example. Suppose you buy a stock for $10, and it goes
up to $20, then pays out a $10 dividend. Now, the current price is $10 and
the adjusted starting price is $5. The calculation in your worksheet of the
total return at that point is:
(End price - Adjusted Start price)/(Unadjusted Start Price) = ($10 - $5) /
($10) = 50%
But you're sitting there with a $10 dividend in your pocket, and you still
own a $10 stock, on a $10 investment. That's a 100% return. I always use
this as total return:
Adjusted end price / Adjusted start price - 1 = $10 / $5 - 1 = 100%
Keep in mind that the "adjusted" prices on Yahoo are just there to provide
context for price comparisons between dates. Yahoo could just as well just
put an adjustment ratio there instead of a price. Instead, we need to
compute that adjustment ratio ourselves, in order to apply it to O/H/L
prices.
===> 2. I have learned to NEVER base my date calculations in this type of
worksheet on the NOW() or TODAY() functions. Those are volatile formulas,
which change any formulas that use them into volatile formulas. That means
ANY change you make anywhere in EXCEL will cause all of those volatile
formulas to recalculate. Even just entering a value into a worksheet cell.
That's a recalculation nightmare. Instead, for "today's" date, I always use:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
...which just gets the last date that SPY was traded.
===> 3. Having said all that, all you need is the adjusted stock prices for
each of the four dates for each stock. That can be accomplished with a
single array-entered smfPricesByDates() function for each stock. Which
turns each set of eight RCHGetYahooQuotes() invocations into a single one
that is done by smfPricesByDates(), which then searches through the
returned data for the adjusted closing prices on the four specified dates.
Then you don't have to worry about the holidays either.
===> 4. You could combine all of the RCHGetYahooQuotes() functions into a
single array-entered one. In the sample worksheet you gave me, that turns
ten Internet accesses into a single one.
===> 5. In the end, you no longer need columns I thru P. Your formulas in
cells A20:D20 could be replaced by a single array-entered:
=F20/smfPricesByDates(H20,$A$19:$D$19)-1
On Wed, Aug 19, 2015 at 11:45 AM, jhfrazer@ymail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have been a fan of your work and relied on it for some time. I have
> been unable to devise a way of reduucing the time it takes a larger version
> of this spreadsheet to calculate. I have included this slammer version so
> that you might enlighten me.
>
> The Total Return formulas are accurate but needing nine separate downloads
> to provide the data for the Total Return Calculations is what is taking the
> time I believe.
>
> Your assistance is greatly appreciated.
>
Wed Aug 19, 2015 5:45 pm (PDT) . Posted by:
jhfrazer@ymail.com
Hi Randy,
I had hoped that you would simply respond to my question. Yahoo adjusts their price by dividends paid. Consider two stocks; ABC and XYZ.
ABC is purchased on Jan 1, 2014 for $10.00. ABC pays a dividend of $0.25 four times throughout 2014 for a total of $1.00. On December 31, 2014 the stock closes at $10.00.
XYZ is purchased on Jan 1, 2014 for $10.00. XYZ pays no throughout 2014. On December 31, 2014 the stock closes at $10.00..
My formula offers an accurate way of including dividends in the total return calculation without summing the dividends.
For ABC the math works as follows (10-9)/10=10% Total Return ((Last or Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
For XYZ the math works as follows (10-10)/10 = 0% Total Return ((Last or Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
These values are in fact correct.
As to your example, with all due respect, Yahoo would show an adjusted price equal to the Unadjusted price less the dividend or on your example $0.00 not $5.00. Please note the Yahoo table below for PG. On July 22 PG goes ex-dividend for $0.663 per share. The Values for July 21 and earlier identify this dividend with a difference of $0.663 cents per share between the Adjusted Price and the Close, the unadjusted price.
Thank you again and I do hope you can help me with my original question regarding an efficient method by which I can access adjusted and unadjusted closing prices for certain past dates. Perhaps 2 arrays one for adjusted past dates and another array for unadjusted past dates.
Kindest,
John
Date Open High Low Close Volume Adjusted
Jul 23, 2015
80.83
80.89
80.14
80.70
5,380,900 80.70 Jul 22, 2015 81.03 81.30 80.67 80.83 6,106,400 80.83 Jul 22, 2015 0.663 Dividend Jul 21, 2015 82.01 82.13 81.37 81.63 7,512,400 80.97 Jul 20, 2015 82.09 82.35 82.00 82.19 4,952,100 81.52
I had hoped that you would simply respond to my question. Yahoo adjusts their price by dividends paid. Consider two stocks; ABC and XYZ.
ABC is purchased on Jan 1, 2014 for $10.00. ABC pays a dividend of $0.25 four times throughout 2014 for a total of $1.00. On December 31, 2014 the stock closes at $10.00.
XYZ is purchased on Jan 1, 2014 for $10.00. XYZ pays no throughout 2014. On December 31, 2014 the stock closes at $10.00..
My formula offers an accurate way of including dividends in the total return calculation without summing the dividends.
For ABC the math works as follows (10-9)/10=10% Total Return ((Last or Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
For XYZ the math works as follows (10-10)/10 = 0% Total Return ((Last or Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
These values are in fact correct.
As to your example, with all due respect, Yahoo would show an adjusted price equal to the Unadjusted price less the dividend or on your example $0.00 not $5.00. Please note the Yahoo table below for PG. On July 22 PG goes ex-dividend for $0.663 per share. The Values for July 21 and earlier identify this dividend with a difference of $0.663 cents per share between the Adjusted Price and the Close, the unadjusted price.
Thank you again and I do hope you can help me with my original question regarding an efficient method by which I can access adjusted and unadjusted closing prices for certain past dates. Perhaps 2 arrays one for adjusted past dates and another array for unadjusted past dates.
Kindest,
John
Date Open High Low Close Volume Adjusted
Jul 23, 2015
80.83
80.89
80.14
80.70
5,380,900 80.70 Jul 22, 2015 81.03 81.30 80.67 80.83 6,106,400 80.83 Jul 22, 2015 0.663 Dividend Jul 21, 2015 82.01 82.13 81.37 81.63 7,512,400 80.97 Jul 20, 2015 82.09 82.35 82.00 82.19 4,952,100 81.52
Wed Aug 19, 2015 9:10 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Items #2 thru #5 WERE all about the efficiencies. Sorry to be bull-headed
about this, but you need to understand the data. See follow-ups within the
body of your message below.
HOWEVER -- something I brought up at the end of the email gave me a thought
for an alternative for you. You could just extract your returns from this
MorningStar web page:
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t=NFLX
That page has the 1-week, 1-month, 3-month, and 1-year total returns -- the
four periods you were interested in. You could just do four extracts from
that web page and only use one Internet access per stock:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,2,">Total
Return",">"&$C4&"<")
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,3,">Total
Return",">"&$C4&"<")
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,4,">Total
Return",">"&$C4&"<")
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,6,">Total
Return",">"&$C4&"<")
On Wed, Aug 19, 2015 at 5:45 PM, jhfrazer@ymail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I had hoped that you would simply respond to my question. Yahoo adjusts
> their price by dividends paid. Consider two stocks; ABC and XYZ.
>
> ABC is purchased on Jan 1, 2014 for $10.00. ABC pays a dividend of $0.25
> four times throughout 2014 for a total of $1.00. On December 31, 2014 the
> stock closes at $10.00.
>
> XYZ is purchased on Jan 1, 2014 for $10.00. XYZ pays no throughout 2014.
> On December 31, 2014 the stock closes at $10.00..
>
> My formula offers an accurate way of including dividends in the total
> return calculation without summing the dividends.
>
> For ABC the math works as follows (10-9)/10=10% Total Return ((Last or
> Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
> For XYZ the math works as follows (10-10)/10 = 0% Total Return ((Last or
> Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
>
> These values are in fact correct.
>
> *As to your example, with all due respect, Yahoo would show an adjusted
> price equal to the Unadjusted price less the dividend or on your example
> $0.00 not $5.00. *
>
Not true. Adjusted prices are done on a percentage basis, not subtraction.
That is equivalent to a subtraction on that date, but any equality on any
other date is coincidental. They may appear to be the same as long as the
price is stable, but that would be true whether it's a subtraction or a
percentage. Check out the section on adjustments here:
https://help.yahoo.com/kb/SLN2311.html
*"Dividend multipliers* are calculated based on dividend as a percentage of
the price, primarily to avoid negative historical pricing."
For an obvious example that subtraction is not taking place, take a look at
SPY's first listed date on Yahoo:
Date Open High Low Close Avg Vol Adj Close* 29-Jan-93 43.97 43.97 43.75
43.94 2,006,400 28.92
But SPY has paid out over $46 in dividends since 1993. If they were simply
subtracting dividends from all history, the adjusted close above would be
negative.
> Please note the Yahoo table below for PG. On July 22 PG goes ex-dividend
> for $0.663 per share. The Values for July 21 and earlier identify this
> dividend with a difference of $0.663 cents per share between the Adjusted
> Price and the Close, the unadjusted price.
>
Only coincidentally, since the price was relatively stable -- HOWEVER, look
at June 5th:
Date Open High Low Close Volume Adj Close* 5-Jun-15 78.04 78.12 77.37 77.43
9,439,700 76.8
The difference between the adjusted close and actual close is $0.63, *NOT
$0.663*. So not a subtraction.
Now look at the date of the dividend payment:
Date Open High Low Close Volume Adj Close* 22-Jul-15 0.663 Dividend
21-Jul-15 82.01 82.13 81.37 81.63 7,512,400 80.97
Here's the *percentage* computation for the Jun 5 adjusted close:
(Adjusted close on Jul 21 / Unadjusted Close on Jul 21) * (Unadjusted Close
on Jun 5) =
($80.97 / $81.63) * ($77.43) = $76.8039581036384
...or the $76.80 you see in the table above. NOT a simple subtraction of
the dividend amount.
It's this percentage adjustment that allows us to be able to take the ratio
of any two adjusted closing prices to compute the return on investment
between the two dates.
Also, note the inaccuracy above -- $80.97 is NOT a correct adjusted price
for Jul 21, since it is a rounded value. But Yahoo recently increased the
accuracy of adjusted close on the CSV download files. It has:
Date Open High Low Close Volume Adj Close 7/21/2015 82.01 82.13 81.37 81.63
7512400 80.967 6/5/2015 78.04 78.12 77.37 77.43 9439700 76.80111
Now, let's try that computation again:
($80.967 / $81.63) * $77.43 = $76.8011124586549
Much better. This was a bothersome issue on stocks with decades of history,
because rounding off to a penny difference on an adjusted price under $1
means big errors.
In reality, both of our methods are just estimates and incorrect. Yours
will be less accurate as dividend amounts get larger and time periods get
longer. And both of ours are incorrect because they use the ex-dividend
date and not the payment date. The correct method would be to determine the
return on investment based on the cash flow during the time period. And
that would mean using the payment date of the dividend, not the ex-dividend
date. Time has value.
For example, suppose I bought a stock for $10 a year ago and sell it for
$10 today and it paid a $1 dividend in between. For a real return
computation, it makes a difference whether that ex-dividend and payment
date were shortly after I bought the stock, or the ex-divided date was a
few days before I sold the stock (payment date could even be months after I
sold the stock). About 11% versus 10%, because if it was paid nearly a year
ago, it was like I bought the stock for $9 ($10/$9 = 11%). But if it was
paid near the end of the year, it's like I got paid more for the stock
($11/$10 = 10%).
> Thank you again and I do hope you can help me with my original question
> regarding an efficient method by which I can access adjusted and unadjusted
> closing prices for certain past dates. Perhaps 2 arrays one for adjusted
> past dates and another array for unadjusted past dates.
>
If you want to continue with the way you do it, you'll need to retrieve the
pertinent history of the stock and look up the values in order to speed it
up.
I've had requests to allow smfPricesByDates() to be able to get the
unadjusted prices, but IMO that would just encourage/allow people to use
the data incorrectly. I can't think of many legitimate reasons to compare
two different unadjusted prices. One obvious example of where this would be
a problem is a stock split in between.
Since I brought up stock splits, let's look at NFLX, which recently split.
Suppose I want a 52-week return as of 7/31:
Date Open High Low Close Volume Adj Close 7/31/2015 111.949997 115
111.150002 114.309998 15591200 114.309998 7/31/2014 430.259995 432.96999
421.289989 422.720009 13820100 60.388573
Your method: ($114.309998 - $60.388573) / $422.720009 = 12.7558%
My method: $114.309998 / $60.388573 - 1 = 89.29%
Note that mine is 7 times yours, because of the 7-for-1 split. Your "end
price" hasn't accounted for the fact you now own 7 shares of stock at that
price. Dividends have the same result. They're just smaller, so the error
isn't as obvious.
BTW, MorningStar reports 89.29% for the 1-year return, as of 7/31/2015
(under the monthly tab):
http://performance.morningstar.com/stock/performance-return.action?t=NFLX
about this, but you need to understand the data. See follow-ups within the
body of your message below.
HOWEVER -- something I brought up at the end of the email gave me a thought
for an alternative for you. You could just extract your returns from this
MorningStar web page:
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t=NFLX
That page has the 1-week, 1-month, 3-month, and 1-year total returns -- the
four periods you were interested in. You could just do four extracts from
that web page and only use one Internet access per stock:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,2,">Total
Return",">"&$C4&"<")
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,3,">Total
Return",">"&$C4&"<")
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,4,">Total
Return",">"&$C4&"<")
=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C4,6,">Total
Return",">"&$C4&"<")
On Wed, Aug 19, 2015 at 5:45 PM, jhfrazer@ymail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I had hoped that you would simply respond to my question. Yahoo adjusts
> their price by dividends paid. Consider two stocks; ABC and XYZ.
>
> ABC is purchased on Jan 1, 2014 for $10.00. ABC pays a dividend of $0.25
> four times throughout 2014 for a total of $1.00. On December 31, 2014 the
> stock closes at $10.00.
>
> XYZ is purchased on Jan 1, 2014 for $10.00. XYZ pays no throughout 2014.
> On December 31, 2014 the stock closes at $10.00..
>
> My formula offers an accurate way of including dividends in the total
> return calculation without summing the dividends.
>
> For ABC the math works as follows (10-9)/10=10% Total Return ((Last or
> Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
> For XYZ the math works as follows (10-10)/10 = 0% Total Return ((Last or
> Close) - Adjusted for 3months ago) / Unadjusted for threee months ago
>
> These values are in fact correct.
>
> *As to your example, with all due respect, Yahoo would show an adjusted
> price equal to the Unadjusted price less the dividend or on your example
> $0.00 not $5.00. *
>
Not true. Adjusted prices are done on a percentage basis, not subtraction.
That is equivalent to a subtraction on that date, but any equality on any
other date is coincidental. They may appear to be the same as long as the
price is stable, but that would be true whether it's a subtraction or a
percentage. Check out the section on adjustments here:
https://help.yahoo.com/kb/SLN2311.html
*"Dividend multipliers* are calculated based on dividend as a percentage of
the price, primarily to avoid negative historical pricing."
For an obvious example that subtraction is not taking place, take a look at
SPY's first listed date on Yahoo:
Date Open High Low Close Avg Vol Adj Close* 29-Jan-93 43.97 43.97 43.75
43.94 2,006,400 28.92
But SPY has paid out over $46 in dividends since 1993. If they were simply
subtracting dividends from all history, the adjusted close above would be
negative.
> Please note the Yahoo table below for PG. On July 22 PG goes ex-dividend
> for $0.663 per share. The Values for July 21 and earlier identify this
> dividend with a difference of $0.663 cents per share between the Adjusted
> Price and the Close, the unadjusted price.
>
Only coincidentally, since the price was relatively stable -- HOWEVER, look
at June 5th:
Date Open High Low Close Volume Adj Close* 5-Jun-15 78.04 78.12 77.37 77.43
9,439,700 76.8
The difference between the adjusted close and actual close is $0.63, *NOT
$0.663*. So not a subtraction.
Now look at the date of the dividend payment:
Date Open High Low Close Volume Adj Close* 22-Jul-15 0.663 Dividend
21-Jul-15 82.01 82.13 81.37 81.63 7,512,400 80.97
Here's the *percentage* computation for the Jun 5 adjusted close:
(Adjusted close on Jul 21 / Unadjusted Close on Jul 21) * (Unadjusted Close
on Jun 5) =
($80.97 / $81.63) * ($77.43) = $76.8039581036384
...or the $76.80 you see in the table above. NOT a simple subtraction of
the dividend amount.
It's this percentage adjustment that allows us to be able to take the ratio
of any two adjusted closing prices to compute the return on investment
between the two dates.
Also, note the inaccuracy above -- $80.97 is NOT a correct adjusted price
for Jul 21, since it is a rounded value. But Yahoo recently increased the
accuracy of adjusted close on the CSV download files. It has:
Date Open High Low Close Volume Adj Close 7/21/2015 82.01 82.13 81.37 81.63
7512400 80.967 6/5/2015 78.04 78.12 77.37 77.43 9439700 76.80111
Now, let's try that computation again:
($80.967 / $81.63) * $77.43 = $76.8011124586549
Much better. This was a bothersome issue on stocks with decades of history,
because rounding off to a penny difference on an adjusted price under $1
means big errors.
In reality, both of our methods are just estimates and incorrect. Yours
will be less accurate as dividend amounts get larger and time periods get
longer. And both of ours are incorrect because they use the ex-dividend
date and not the payment date. The correct method would be to determine the
return on investment based on the cash flow during the time period. And
that would mean using the payment date of the dividend, not the ex-dividend
date. Time has value.
For example, suppose I bought a stock for $10 a year ago and sell it for
$10 today and it paid a $1 dividend in between. For a real return
computation, it makes a difference whether that ex-dividend and payment
date were shortly after I bought the stock, or the ex-divided date was a
few days before I sold the stock (payment date could even be months after I
sold the stock). About 11% versus 10%, because if it was paid nearly a year
ago, it was like I bought the stock for $9 ($10/$9 = 11%). But if it was
paid near the end of the year, it's like I got paid more for the stock
($11/$10 = 10%).
> Thank you again and I do hope you can help me with my original question
> regarding an efficient method by which I can access adjusted and unadjusted
> closing prices for certain past dates. Perhaps 2 arrays one for adjusted
> past dates and another array for unadjusted past dates.
>
If you want to continue with the way you do it, you'll need to retrieve the
pertinent history of the stock and look up the values in order to speed it
up.
I've had requests to allow smfPricesByDates() to be able to get the
unadjusted prices, but IMO that would just encourage/allow people to use
the data incorrectly. I can't think of many legitimate reasons to compare
two different unadjusted prices. One obvious example of where this would be
a problem is a stock split in between.
Since I brought up stock splits, let's look at NFLX, which recently split.
Suppose I want a 52-week return as of 7/31:
Date Open High Low Close Volume Adj Close 7/31/2015 111.949997 115
111.150002 114.309998 15591200 114.309998 7/31/2014 430.259995 432.96999
421.289989 422.720009 13820100 60.388573
Your method: ($114.309998 - $60.388573) / $422.720009 = 12.7558%
My method: $114.309998 / $60.388573 - 1 = 89.29%
Note that mine is 7 times yours, because of the 7-for-1 split. Your "end
price" hasn't accounted for the fact you now own 7 shares of stock at that
price. Dividends have the same result. They're just smaller, so the error
isn't as obvious.
BTW, MorningStar reports 89.29% for the 1-year return, as of 7/31/2015
(under the monthly tab):
http://performance.morningstar.com/stock/performance-return.action?t=NFLX
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar