Jumat, 22 Desember 2017

[smf_addin] Digest Number 4272

11 Messages

Digest #4272
1b
1c
Re: Website to extract dividend frequency by "Randy Harmelink" rharmelink
2
Happy Holidays by "Fred Wright" fredwwright
3.1
smfForceRecalculation by bob_15861586
3.2
Re: smfForceRecalculation by "Randy Harmelink" rharmelink
4b
Re: Hong Kong Stock Options Data by "Randy Harmelink" rharmelink
4d
Re: Hong Kong Stock Options Data by "Randy Harmelink" rharmelink

Messages

Fri Dec 22, 2017 7:36 am (PST) . Posted by:

a.bereziuk

Array entering this
=COUNT(smfGetYahooHistory(C4,B4-365,+B4,"v",,0,,20,2))/2
brings this:

2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
And array entering this
=4*CEILING(COUNT(smfGetYahooHistory(C4, B4-365,B4,"v",,0,,20,2))/2/4,1)
brings this:

4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
Sorry, I don't understand why "COUNT" and why "CEILING". Even when I get dividend dates and paid dividends on these dates, as a table - this is not what I would like to achieve.
I just need a value of *dividend frequency* in *one cell*. And this would be: "monthly", "quarterly"... etc. Or numeric values accordingly which I can then replace with appropriate strings.

Fri Dec 22, 2017 7:55 am (PST) . Posted by:

"Jose Jacob" pepecan47

Would it help?
McDonald's Corp (MCD) Dividend Data -- GuruFocus.com 

|
|
|
| | |

|

|
|
| |
McDonald's Corp (MCD) Dividend Data -- GuruFocus.com
McDonald's Corp (MCD) Dividend yield, dividend payout, dividend growth rate and historical dividend data McD... | |

|

|

Regards, Jose L. Jacob

On Friday, December 22, 2017 10:36 AM, "a.bereziuk@yahoo.de [smf_addin]" <smf_addin@yahoogroups.com> wrote:


  Array entering this
=COUNT(smfGetYahooHistory(C4,B4-365,+B4,"v",,0,,20,2))/2
brings this:

2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
And array entering this
=4*CEILING(COUNT(smfGetYahooHistory(C4, B4-365,B4,"v",,0,,20,2))/2/4,1)
brings this:

4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
Sorry, I don't understand why "COUNT" and why "CEILING". Even when I get dividend dates and paid dividends on these dates, as a table - this is not what I would like to achieve.
I just need a value of *dividend frequency* in *one cell*. And this would be: "monthly", "quarterly&quot;... etc. Or numeric values accordingly which I can then replace with appropriate strings. #yiv5202835137 #yiv5202835137 -- #yiv5202835137ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5202835137 #yiv5202835137ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5202835137 #yiv5202835137ygrp-mkp #yiv5202835137hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5202835137 #yiv5202835137ygrp-mkp #yiv5202835137ads {margin-bottom:10px;}#yiv5202835137 #yiv5202835137ygrp-mkp .yiv5202835137ad {padding:0 0;}#yiv5202835137 #yiv5202835137ygrp-mkp ..yiv5202835137ad p {margin:0;}#yiv5202835137 #yiv5202835137ygrp-mkp .yiv5202835137ad a {color:#0000ff;text-decoration:none;}#yiv5202835137 #yiv5202835137ygrp-sponsor #yiv5202835137ygrp-lc {font-family:Arial;}#yiv5202835137 #yiv5202835137ygrp-sponsor #yiv5202835137ygrp-lc #yiv5202835137hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5202835137 #yiv5202835137ygrp-sponsor #yiv5202835137ygrp-lc .yiv5202835137ad {margin-bottom:10px;padding:0 0;}#yiv5202835137 #yiv5202835137actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5202835137 #yiv5202835137activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5202835137 #yiv5202835137activity span {font-weight:700;}#yiv5202835137 #yiv5202835137activity span:first-child {text-transform:uppercase;}#yiv5202835137 #yiv5202835137activity span a {color:#5085b6;text-decoration:none;}#yiv5202835137 #yiv5202835137activity span span {color:#ff7900;}#yiv5202835137 #yiv5202835137activity span .yiv5202835137underline {text-decoration:underline;}#yiv5202835137 .yiv5202835137attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5202835137 .yiv5202835137attach div a {text-decoration:none;}#yiv5202835137 .yiv5202835137attach img {border:none;padding-right:5px;}#yiv5202835137 ..yiv5202835137attach label {display:block;margin-bottom:5px;}#yiv5202835137 .yiv5202835137attach label a {text-decoration:none;}#yiv5202835137 blockquote {margin:0 0 0 4px;}#yiv5202835137 .yiv5202835137bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5202835137 .yiv5202835137bold a {text-decoration:none;}#yiv5202835137 dd.yiv5202835137last p a {font-family:Verdana;font-weight:700;}#yiv5202835137 dd.yiv5202835137last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5202835137 dd.yiv5202835137last p span.yiv5202835137yshortcuts {margin-right:0;}#yiv5202835137 div.yiv5202835137attach-table div div a {text-decoration:none;}#yiv5202835137 div.yiv5202835137attach-table {width:400px;}#yiv5202835137 div.yiv5202835137file-title a, #yiv5202835137 div.yiv5202835137file-title a:active, #yiv5202835137 div.yiv5202835137file-title a:hover, #yiv5202835137 div.yiv5202835137file-title a:visited {text-decoration:none;}#yiv5202835137 div.yiv5202835137photo-title a, #yiv5202835137 div.yiv5202835137photo-title a:active, #yiv5202835137 div.yiv5202835137photo-title a:hover, #yiv5202835137 div.yiv5202835137photo-title a:visited {text-decoration:none;}#yiv5202835137 div#yiv5202835137ygrp-mlmsg #yiv5202835137ygrp-msg p a span.yiv5202835137yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5202835137 .yiv5202835137green {color:#628c2a;}#yiv5202835137 .yiv5202835137MsoNormal {margin:0 0 0 0;}#yiv5202835137 o {font-size:0;}#yiv5202835137 #yiv5202835137photos div {float:left;width:72px;}#yiv5202835137 #yiv5202835137photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv5202835137 #yiv5202835137photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5202835137 #yiv5202835137reco-category {font-size:77%;}#yiv5202835137 #yiv5202835137reco-desc {font-size:77%;}#yiv5202835137 .yiv5202835137replbq {margin:4px;}#yiv5202835137 #yiv5202835137ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5202835137 #yiv5202835137ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5202835137 #yiv5202835137ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5202835137 #yiv5202835137ygrp-mlmsg select, #yiv5202835137 input, #yiv5202835137 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5202835137 #yiv5202835137ygrp-mlmsg pre, #yiv5202835137 code {font:115% monospace;}#yiv5202835137 #yiv5202835137ygrp-mlmsg * {line-height:1.22em;}#yiv5202835137 #yiv5202835137ygrp-mlmsg #yiv5202835137logo {padding-bottom:10px;}#yiv5202835137 #yiv5202835137ygrp-msg p a {font-family:Verdana;}#yiv5202835137 #yiv5202835137ygrp-msg p#yiv5202835137attach-count span {color:#1E66AE;font-weight:700;}#yiv5202835137 #yiv5202835137ygrp-reco #yiv5202835137reco-head {color:#ff7900;font-weight:700;}#yiv5202835137 #yiv5202835137ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5202835137 #yiv5202835137ygrp-sponsor #yiv5202835137ov li a {font-size:130%;text-decoration:none;}#yiv5202835137 #yiv5202835137ygrp-sponsor #yiv5202835137ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5202835137 #yiv5202835137ygrp-sponsor #yiv5202835137ov ul {margin:0;padding:0 0 0 8px;}#yiv5202835137 #yiv5202835137ygrp-text {font-family:Georgia;}#yiv5202835137 #yiv5202835137ygrp-text p {margin:0 0 1em 0;}#yiv5202835137 #yiv5202835137ygrp-text tt {font-size:120%;}#yiv5202835137 #yiv5202835137ygrp-vital ul li:last-child {border-right:none !important;}#yiv5202835137

Fri Dec 22, 2017 9:06 am (PST) . Posted by:

"Randy Harmelink" rharmelink

You're making this so much harder than it needs to be. Something like this:

=​smfGetYahooHistory(C4,B4-365,+B4,"v",,0,,20,2)

....needs to be entered over a range, because it returns an ARRAY of data.
If you just enter it into a single cell, all you'll get is the data item in
the first row and first column of that 20-row by 2-column range.

This would just be entered into a single cell:

=​COUNT(​​smfGetYahooHistory(C4,B4-365,+B4,"v",,0,,20,2))/2

....because it is counting the number of entries in that 20-row by 2-column
array, and then dividing by two, because the returned data is PAIRS of
dates and dividend amounts. And you want to know how many pairs of dates
and amounts there are.

The reason for this:

=4*CEILING(COUNT(smfGetYahooHistory(C4, B4-365,B4,"v",,0,,20,2))/2/4,1)

....is that it may return three pairs or eleven pairs of dates and dividend
amounts. By dividing the previous formula by four, taking the ceiling and
multiplying by four, we're telling it to go UP to the next even multiple of
four, because the dividends may not have fallen within an exact 365-day
period. Another way to solve that is to not use the CEILING() function and
go for the 375 day period, as suggested by Higrm higrm. That should allow
for some leeway on differing endpoint dates, without allowing an additional
monthly ex-dividend date to creep in on those items that have monthly
dividends.

Note that the use of the CEILING() function does assume either quarterly or
monthly dividends. If you actually have stocks with semi-annual dividends,
then it is not a viable option.

On Fri, Dec 22, 2017 at 8:35 AM, a.bereziuk@
​...
wrote:

>
> ​Array entering this
> =
> ​​
> COUNT(
> ​​
> smfGetYahooHistory(C4,B4-365,+B4,"v",,0,,20,2))/2
> brings this:
>
> 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
> And array entering this
> =4*CEILING(COUNT(smfGetYahooHistory(C4, B4-365,B4,"v",,0,,20,2))/2/4,1)
> brings this:
>
> 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
> Sorry, I don't understand why "COUNT" and why "CEILING". Even when I get
> dividend dates and paid dividends on these dates, as a table - this is not
> what I would like to achieve.
> I just need a value of *dividend frequency* in *one cell*. And this would
> be: "monthly", "quarterly&quot;... etc. Or numeric values accordingly which I
> can then replace with appropriate strings.
>
>

Fri Dec 22, 2017 11:16 am (PST) . Posted by:

a.bereziuk

Thanks! Great explanation, now everything is clear. I will add this post to my documentation, together with this one: groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114

>I don't understand why it would work at all. Those are invalid dates.
Those were European dates and delimiters in Excel ))

Fri Dec 22, 2017 1:59 pm (PST) . Posted by:

"Fred Wright" fredwwright

Randy and everyone on this list - thanks for this great add-in and all the help.

|
|
| |

|
|
| Happy Holidays and best wishes for Health, Wealth and Happinessin the New Year |

|
|
| |

|

|
|
| Yahoo Mail Stationery |

|

Fri Dec 22, 2017 2:11 pm (PST) . Posted by:

bob_15861586

My understanding is that I should be able to create a short cut in Excel to run smfForceRecalculation. How do I do that?


Thanks,
Bob

Fri Dec 22, 2017 3:20 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

See:

https://support.office.com/en-us/article/Assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c

On Fri, Dec 22, 2017 at 3:11 PM, bob_15861586@
​...
wrote:

> My understanding is that I should be able to create a short cut in Excel
> to run smfForceRecalculation. How do I do that?
>
>
>

Fri Dec 22, 2017 3:10 pm (PST) . Posted by:

alankc_mok

Dear Randy,

Lately, I have another questions regarding data from this site, i.e., I want to extract the IV and HV
https://www.hkex.com.hk/eng/sorc/options/statistics_hv_iv.aspx https://www..hkex.com.hk/eng/sorc/options/statistics_hv_iv.aspx



It will allow downloading though, but I am trying to see if there is any smarter method using the SMF_Addin ?


Regards


Alan


Fri Dec 22, 2017 4:48 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I think you can just use something like this to grab the file:

=smfGetCSVFile("
https://www.hkex.com.hk/eng/sorc/options/statistics_hv_iv.aspx?action=csv&type=3&ucode=00001
")

On Fri, Dec 22, 2017 at 4:10 PM, alankc_mok@
​...
wrote:

>
> Lately, I have another questions regarding data from this site, i.e., I
> want to extract the IV and HV
> https://www.hkex.com.hk/eng/sorc/options/statistics_hv_iv.aspx
>
> It will allow downloading though, but I am trying to see if there is any
> smarter method using the SMF_Addin ?
>
>
>

Fri Dec 22, 2017 5:34 pm (PST) . Posted by:

alankc_mok

Randy,

Thanks for your prompt reply.


The symbol 00001 is working, but when I try the symbol 00700 (Tencent), the CSV file cannot be downloaded.


Regards


Alan

Fri Dec 22, 2017 5:43 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

This worked fine here, without me doing anything other than a copy, paste,
and change of the symbol:

=smfGetCSVFile("
https://www.hkex.com.hk/eng/sorc/options/statistics_hv_iv.aspx?action=csv&type=3&ucode=00700
")

On Fri, Dec 22, 2017 at 6:33 PM, alankc_mok@
​...
wrote:

>
> Thanks for your prompt reply.
>
> The symbol 00001 is working, but when I try the symbol 00700 (Tencent),
> the CSV file cannot be downloaded.
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 18.12

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)