[smf_addin] Digest Number 4171

EXCEL Stock Market Functions Add-in Group

5 Messages

Digest #4171

1a

Re: OX and OX2 data sources not working by "Randy Harmelink" rharmelink

2a

Morningstar Dividend Data Extraction by ridgebacksexcel

2b

Re: Morningstar Dividend Data Extraction by "Randy Harmelink" rharmelink

2c

Re: Morningstar Dividend Data Extraction by ridgebacksexcel

3a

Re: RCH Element Functions by peggysue281

Messages

1a

Re: OX and OX2 data sources not working

Sat Oct 14, 2017 3:28 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You'd need to use the Yahoo option ticker symbols.

But you can create the ticker list for the formula using smfJoin() over the
range of ticker symbols.

I've done the processing in a half dozen workbooks and I do each one a
little differently. So far, I think the best design is to have one sheet
that does nothing more than get the quotes. Then other sheets can refer to
the returned data on that worksheet. Unfortunately, that means maintaining
the ticker symbol in (at least) two places. Otherwise, it creates a
circular reference.

And stocks, ETFs, mutual funds, and option quotes each have some unique
fields.

At some point, I may create a version of RCHGetYahooQuotes() that grabs the
data from the JSON portfolioView file instead.

For example, the "GetQuotes" worksheet for my covered call blog
<https://wrongwaycoveredcalls.blogspot.com/> looks like this:

*Tickers* *Time* *Price* *$Chg* *%Chg* *Volume* *Bid* *Ask* *Yield*
*Div/Share* *Ex-Div Date*
MMM 2017-10-13 04:01 PM $217.72 $0.13 0.06% 1,461,703 -- -- 2.18% $4.57
2017-08-23
-- -- -- -- -- -- -- -- -- --
ED 2017-10-13 04:01 PM $83.08 -$0.45 -0.54% 1,558,466 -- -- 3.38% $2.72
2017-08-14
MCD 2017-10-13 04:00 PM $165.37 $1.46 0.89% 2,957,929 -- -- 2.58% $3.71
2017-11-30
NWN 2017-10-13 04:02 PM $65.55 -$0.45 -0.68% 84,602 -- -- 2.90% $1.88
2017-07-27
-- -- -- -- -- -- -- -- -- --
SPHD 2017-10-13 04:00 PM $41.68 $0.09 0.22% 249,496 -- -- 3.60% -- --
-- -- -- -- -- -- -- -- -- --
PG 2017-10-13 04:00 PM $93.04 $0.89 0.97% 6,956,656 -- -- 3.03% $2.70
2017-07-19
PG171020C00091000 2017-10-13 03:15 PM $2.29 $0.89 63.57% 66 $2.02 $2.26 --
-- --
-- -- -- -- -- -- -- -- -- --
NUE 2017-10-13 04:01 PM $58.31 $0.89 1.55% 2,268,272 -- -- 2.70% $1.51
2017-09-28
NUE171027C00059000 2017-10-13 10:57 AM $0.87 $0.32 58.18% 179 $0.79 $0.85 --
-- --
-- -- -- -- -- -- -- -- -- --
WFC 2017-10-13 04:00 PM $53.69 -$1.52 -2.75% 35,310,028 -- -- 2.84% $1.52
2017-08-02
WFC171117C00057500 2017-10-13 03:59 PM $0.09 -$0.28 -75.68% 822 $0.08 $0.11
-- -- --
-- -- -- -- -- -- -- -- -- --
SPY 2017-10-13 04:00 PM $254.91 $0.27 0.11% 84,087,179 -- -- 1.90% -- --
IWM 2017-10-13 04:00 PM $149.39 -$0.25 -0.17% 24,284,353 -- -- 1.29% -- --
DIA 2017-10-13 04:00 PM $228.73 $0.40 0.18% 1,744,317 -- -- 2.11% -- --
-- -- -- -- -- -- -- -- -- --
LLY 2017-10-13 04:00 PM $86.55 $0.12 0.14% 3,027,228 -- -- 2.41% $2.06
2017-08-11
MDT 2017-10-13 04:01 PM $78.07 $0.15 0.19% 5,442,264 -- -- 2.36% $1.75
2017-09-28
VZ 2017-10-13 04:01 PM $47.86 -$0.49 -1.01% 20,105,054 -- -- 4.73% $2.31
2017-07-06
-- -- -- -- -- -- -- -- -- --
HD171124C00160000 2017-10-13 01:38 PM $6.88 -$0.04 -0.58% 1 $6.00 $7.00 --
-- --
-- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- --

​So all of that data is retrieved with just one Internet access.​

On Fri, Oct 13, 2017 at 7:19 PM, eabartsch@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Thanks. That is fascinating. I wonder. . .how do you crunch the
> Portfolio view across a larger set of standardized data? Let's say you
> have:
>
> LOW 10/20 2017 73 Put
> MMM 10/20 2017 110 Put
> etc. etc.
>
> in a column. Right now, I just pull the data from the column in an array
> entered GetOptionQuotes function and see the results. But, with Yahoo data
> being limited to only a small number of queries per IP address (and
> otherwise behaving intermittently), and the Barchart data (for some reason)
> not pulling up prices on my spreadsheet (although underlying returns a
> value), I'm intrigued by your JSON fix.
>
>
>

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

2a

Morningstar Dividend Data Extraction

Sat Oct 14, 2017 9:30 am (PDT) . Posted by:

ridgebacksexcel

Randy,


I currently use a formula you previously gave me to extract the current dividend for stocks from this page.
AAPL in this example:


http://quotes.morningstar.com/stock/c-dividends?&t=aapl http://quotes.morningstar.com/stock/c-dividends?&t=aapl


The formula where B14 is "AAPL":


=IF($B14="","",RCHGetTableCell("http://quotes.morningstar.com/stock/c-dividends?&t="&$B14,1,"<tbody","Latest Indicated Dividend Amount"))


How would I write a formula to extract the Dates and Amounts for the 4 Recent Dividends below?
I can't seem to get anything to work.


Date Type Amount 08/10/2017 Cash Dividends 0.6300 05/11/2017 Cash Dividends 0.6300 02/09/2017 Cash Dividends 0.5700 11/03/2016 Cash Dividends 0.5700

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

2b

Re: Morningstar Dividend Data Extraction

Sat Oct 14, 2017 11:04 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Try:

=smfGetTagContent("http://quotes.morningstar.com/stock/c-dividends?&t="&$B14,"td",4,"Recent
Dividend")

Just increase the 4 to get other fields in the table. The first row would
be items 4, 5, and 6.

On Sat, Oct 14, 2017 at 9:30 AM, tmallen2@bellsouth.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> I currently use a formula you previously gave me to extract the current
> dividend for stocks from this page.
>
> AAPL in this example:
>
> http://quotes.morningstar.com/stock/c-dividends?&t=aapl
>
> The formula where B14 is "AAPL":
>
> =IF($B14="","",RCHGetTableCell("http://quotes.morningstar.com/stock/
> c-dividends?&t="&$B14,1,"<tbody","Latest Indicated Dividend Amount"))
>
> How would I write a formula to extract the Dates and Amounts for the 4
> Recent Dividends below?
>
> I can't seem to get anything to work.
> Date Type Amount
> 08/10/2017 Cash Dividends 0.6300
> 05/11/2017 Cash Dividends 0.6300
> 02/09/2017 Cash Dividends 0.5700
> 11/03/2016 Cash Dividends 0.5700
>
>
>

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

2c

Re: Morningstar Dividend Data Extraction

Sat Oct 14, 2017 11:12 am (PDT) . Posted by:

ridgebacksexcel

Thank you Randy. That fixed it.

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

3a

Re: RCH Element Functions

Sat Oct 14, 2017 11:22 am (PDT) . Posted by:

peggysue281

Correct, I unblocked the Add-in and I tried checking and un-checking the Add-in in my excel options but still no luck.

I think I found a solution though. If I open a blank excel workbook first, unblock the Add-in file and then open my workbook spreadsheet the formulas automatically calculate.

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

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

Visit Your Group

• New Members 10

• Privacy • Unsubscribe • Terms of Use