[smf_addin] Digest Number 4268

EXCEL Stock Market Functions Add-in Group

4 Messages

Digest #4268

1a

did Ycharts recently make some changes? by zbgb952

1b

Re: did Ycharts recently make some changes? by "Randy Harmelink" rharmelink

2a

smfGetOptionExpirations - bad/no data from Yahoo by bobobfd

2b

Re: smfGetOptionExpirations - bad/no data from Yahoo by "Randy Harmelink" rharmelink

Messages

1a

did Ycharts recently make some changes?

Sun Dec 17, 2017 9:26 pm (PST) . Posted by:

zbgb952

In my Excel spreadsheet, I use the smf function IF($D12="","",RCHGetTableCell( "https://ycharts.com/companies/" & $D12,1,"Dividend Yield (TTM)" ) ) , where D12 refers to a ticker symbol. This is look for the the dividend yield of the stock. I also have some other functions like this but all are now returning the same value "upgrade". What??!? No more data available at Ycharts?

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

1b

Re: did Ycharts recently make some changes?

Sun Dec 17, 2017 9:41 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

If you look at their web page, a lot of items now have an "Upgrade" link
shown...

https://ycharts.com/companies/MMM

On Sun, Dec 17, 2017 at 10:26 PM, mb0329@
​...
wrote:

>
> In my Excel spreadsheet, I use the smf function
> IF($D12="","",RCHGetTableCell( "https://ycharts.com/companies/" &
> $D12,1,"Dividend Yield (TTM)" ) ) , where D12 refers to a ticker symbol.
> This is look for the the dividend yield of the stock. I also have some
> other functions like this but all are now returning the same value
> "upgrade". What??!? No more data available at Ycharts?
>
>
>

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

2a

smfGetOptionExpirations - bad/no data from Yahoo

Mon Dec 18, 2017 2:11 pm (PST) . Posted by:

bobobfd

Couple of things re smfGetOptionExpirations :

1. Sometimes Yahoo's data is missing or just wrong. For ex.
- BUD options for Feb '18 don't exist. (they do),
- SBUX has an option date of 03/20/2026.

I know you can't control what Yahoo gives you -gigo - but isn't there a better source for it, ie, nasdaq.com, barchart, morning star, etc.??

2. Is it possible to filter the returned option expirations by type - weekly, monthly, all?? Trying to get a list of only monthly expirations by formula is cumbersome at best. (No knowledge of VBA.)

Thank you. much ~ Bruce

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

2b

Re: smfGetOptionExpirations - bad/no data from Yahoo

Mon Dec 18, 2017 3:48 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

For (1), here's what I get for BUD and SBUX:

BUD SBUX
2017-12-22 2017-12-22
2017-12-29 2017-12-29
2018-01-05 2018-01-05
2018-01-12 2018-01-12
2018-01-19 2018-01-19
2018-01-26 2018-01-26
2018-02-16 2018-02-16
2018-03-16 2018-03-16
2018-06-15 2018-04-20
2019-01-18 2018-06-15
2020-01-17 2018-07-20
2018-09-21
2019-01-18
2020-01-17
2026-03-20

​...so I am seeing the Feb 2018 expiration date for BUD. But I also show
the SBUX 2026 date (which has a web page on Yahoo with no option contracts
listed).​ But, yes, GIGO applies. I can revisit the other sources when I
have some time, but lately Yahoo has been working great for my needs. IIRC,
Morningstar unplugged their option quotes a few years ago. Barchart and
NASDAQ both had issues last time I looked, which is why they go to Yahoo.
OptionsXPress used to be an alternative, but they are gone now too. Since
they actually traded the options, they were the most accurate source, but
their website retrieval times were sooooooo SLOW. :(

For (2), I had experimented with that on OX, because they labeled (some of)
them. So there is a parameter in the function right now (but not being used
for any active source). I had looked at "A"ll, "W"eekly, "M"onthly,
"Q"uarterly. But applying them to contracts in general can be problematic.
These examples show most of my issues:

SPY Q? M? W? H? V? ^VIX Q? M? W? H? V? MMM Q? M? W? H? V?
2017-12-20 -- -- -- H V 2017-12-20 -- -- -- H V 2017-12-22 -- M W -- --
2017-12-22 -- M W -- -- 2017-12-27 -- -- -- H -- 2017-12-29 Q -- W -- --
2017-12-27 -- -- -- H -- 2018-01-03 -- -- -- H -- 2018-01-05 -- -- W --
--
2017-12-29 Q -- W -- -- 2018-01-10 -- -- -- H -- 2018-01-12 -- -- W --
--
2018-01-03 -- -- -- H -- 2018-01-17 -- -- -- H V 2018-01-19 -- M W -- --
2018-01-05 -- -- W -- -- 2018-01-24 -- -- -- H -- 2018-01-26 -- -- W --
--
2018-01-10 -- -- -- H -- 2018-02-14 -- -- -- -- -- 2018-02-16 -- M -- --
--
2018-01-12 -- -- W -- -- 2018-03-21 -- -- -- -- V 2018-03-16 -- M -- --
--
2018-01-17 -- -- -- H V 2018-04-18 -- -- -- -- V 2018-04-20 -- M -- --
--
2018-01-19 -- M W -- -- 2018-05-16 -- -- -- -- V 2018-06-15 -- M -- --
--
2018-01-26 -- -- W -- -- -- -- -- -- -- 2018-07-20 -- M -- -- --
2018-02-16 -- M -- -- -- -- -- -- -- -- 2018-09-21 -- M -- -- --
2018-03-16 -- M -- -- -- -- -- -- -- -- 2019-01-18 -- M -- -- --
2018-03-29 Q -- -- -- -- -- -- -- -- -- 2020-01-17 -- M -- -- --
2018-04-20 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2018-06-15 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2018-06-29 Q -- -- -- -- -- -- -- -- -- -- -- -- -- --
2018-07-20 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2018-09-21 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2018-09-28 Q -- -- -- -- -- -- -- -- -- -- -- -- -- --
2018-12-21 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2019-01-18 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2019-03-15 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2019-06-21 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2019-12-20 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2020-01-17 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
2020-07-17 -- M -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Q = Quarterly
M = Monthly (on Friday)
W = Weekly (on Friday)
H = Weekly (on Wednesday)
V = Monthly (on Wednesday)

Issues:
-- 2017-12-29 classified as quarterly on MMM
-- 2018-02-14 has no classification on ^VIX
-- Holidays can change the Friday or Wednesday expiration day (always to
Thursday and Tuesday?)
-- Should the long term (not necessarily LEAPS) be identified?

These are the formulas I'm running on the returned array of expiration
dates:

=IFERROR(IF((DAY($C$4:$C$32)>27)*(MOD(MONTH($C$4:$C$32),3)=0),"Q","--"),"--")
=IFERROR(IF((DAY($C$4:$C$32)>14)*(DAY($C$4:$C$32)<23)*(WEEKDAY($C$4:$C$32)=6),"M","--"),"--")
=IFERROR(IF(($C$4:$C$32<=$C$4+43)*(WEEKDAY($C$4:$C$32)=6),"W","--"),"--")
=IFERROR(IF(($C$4:$C$32<=$C$4+43)*(WEEKDAY($C$4:$C$32)=4),"H","--"),"--")
=IFERROR(IF((DAY($C$4:$C$32)>14)*(DAY($C$4:$C$32)<23)*(WEEKDAY($C$4:$C$32)=4),"V","--"),"--")

On Mon, Dec 18, 2017 at 3:11 PM, bobobfd@
​...
wrote:

>
> Couple of things re smfGetOptionExpirations :
>
> 1. Sometimes Yahoo's data is missing or just wrong. For ex.
> - BUD options for Feb '18 don't exist. (they do),
> - SBUX has an option date of 03/20/2026.
>
> I know you can't control what Yahoo gives you -gigo - but isn't there a
> better source for it, ie, nasdaq.com, barchart, morning star, etc.??
>
> 2. Is it possible to filter the returned option expirations by type -
> weekly, monthly, all?? Trying to get a list of only monthly expirations by
> formula is cumbersome at best. (No knowledge of VBA.)
>
>

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

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 3

• Privacy • Unsubscribe • Terms of Use