Rabu, 25 Oktober 2017

[smf_addin] Digest Number 4181[1 Attachment]

10 Messages

Digest #4181
1a
Re: smfPricesByDates() by "Ron Spruell" hashky
2b
3a
Re: JSON Data by "Dick Hurlburt" rtexican
3b
Re: JSON Data [1 Attachment] by "Randy Harmelink" rharmelink
4a
4b
Re: Option Expirations? by "Randy Harmelink" rharmelink
5b
Re: Excel 365 compatibility by "Randy Harmelink" rharmelink

Messages

Wed Oct 25, 2017 6:35 am (PDT) . Posted by:

"Ron Spruell" hashky

Whatever shut down my computer during the storm on Sunday also corrupted Excel.  I had to repair the installation of Excel to get it to work.Start->Control Panel->Programs and Features->Microsoft Office ...->Change->Repair
All is well now.  Thanks.

From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, October 24, 2017 6:36 PM
Subject: Re: [smf_addin] smfPricesByDates()

  Not sure what to tell you. This is working fine here:
=smfPricesByDates("SPY","10/13/2017","10/20/2017")

On Tue, Oct 24, 2017 at 4:22 PM, Ron Spruell hashky@​... wrote:

Yahoo strikes again?.  I can't get this to work.
SMFPricesByDates()
Windows 7Excel 2010

| Stock Market Functions add-in, Version 2.1.2017.09.17 (C:\SMF Add-In; Windows (32-bit) NT 6.01; 14.0; ; Local; 1)

I had a major computer crash on Sunday.  Something could be not working here.
|

#yiv4371049915 #yiv4371049915 -- #yiv4371049915ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4371049915 #yiv4371049915ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4371049915 #yiv4371049915ygrp-mkp #yiv4371049915hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4371049915 #yiv4371049915ygrp-mkp #yiv4371049915ads {margin-bottom:10px;}#yiv4371049915 #yiv4371049915ygrp-mkp .yiv4371049915ad {padding:0 0;}#yiv4371049915 #yiv4371049915ygrp-mkp .yiv4371049915ad p {margin:0;}#yiv4371049915 #yiv4371049915ygrp-mkp .yiv4371049915ad a {color:#0000ff;text-decoration:none;}#yiv4371049915 #yiv4371049915ygrp-sponsor #yiv4371049915ygrp-lc {font-family:Arial;}#yiv4371049915 #yiv4371049915ygrp-sponsor #yiv4371049915ygrp-lc #yiv4371049915hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4371049915 #yiv4371049915ygrp-sponsor #yiv4371049915ygrp-lc .yiv4371049915ad {margin-bottom:10px;padding:0 0;}#yiv4371049915 #yiv4371049915actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4371049915 #yiv4371049915activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4371049915 #yiv4371049915activity span {font-weight:700;}#yiv4371049915 #yiv4371049915activity span:first-child {text-transform:uppercase;}#yiv4371049915 #yiv4371049915activity span a {color:#5085b6;text-decoration:none;}#yiv4371049915 #yiv4371049915activity span span {color:#ff7900;}#yiv4371049915 #yiv4371049915activity span .yiv4371049915underline {text-decoration:underline;}#yiv4371049915 .yiv4371049915attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4371049915 .yiv4371049915attach div a {text-decoration:none;}#yiv4371049915 .yiv4371049915attach img {border:none;padding-right:5px;}#yiv4371049915 .yiv4371049915attach label {display:block;margin-bottom:5px;}#yiv4371049915 .yiv4371049915attach label a {text-decoration:none;}#yiv4371049915 blockquote {margin:0 0 0 4px;}#yiv4371049915 .yiv4371049915bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4371049915 .yiv4371049915bold a {text-decoration:none;}#yiv4371049915 dd.yiv4371049915last p a {font-family:Verdana;font-weight:700;}#yiv4371049915 dd.yiv4371049915last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4371049915 dd.yiv4371049915last p span.yiv4371049915yshortcuts {margin-right:0;}#yiv4371049915 div.yiv4371049915attach-table div div a {text-decoration:none;}#yiv4371049915 div.yiv4371049915attach-table {width:400px;}#yiv4371049915 div.yiv4371049915file-title a, #yiv4371049915 div.yiv4371049915file-title a:active, #yiv4371049915 div.yiv4371049915file-title a:hover, #yiv4371049915 div.yiv4371049915file-title a:visited {text-decoration:none;}#yiv4371049915 div.yiv4371049915photo-title a, #yiv4371049915 div.yiv4371049915photo-title a:active, #yiv4371049915 div.yiv4371049915photo-title a:hover, #yiv4371049915 div.yiv4371049915photo-title a:visited {text-decoration:none;}#yiv4371049915 div#yiv4371049915ygrp-mlmsg #yiv4371049915ygrp-msg p a span.yiv4371049915yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4371049915 .yiv4371049915green {color:#628c2a;}#yiv4371049915 .yiv4371049915MsoNormal {margin:0 0 0 0;}#yiv4371049915 o {font-size:0;}#yiv4371049915 #yiv4371049915photos div {float:left;width:72px;}#yiv4371049915 #yiv4371049915photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv4371049915 #yiv4371049915photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4371049915 #yiv4371049915reco-category {font-size:77%;}#yiv4371049915 #yiv4371049915reco-desc {font-size:77%;}#yiv4371049915 .yiv4371049915replbq {margin:4px;}#yiv4371049915 #yiv4371049915ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4371049915 #yiv4371049915ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4371049915 #yiv4371049915ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4371049915 #yiv4371049915ygrp-mlmsg select, #yiv4371049915 input, #yiv4371049915 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4371049915 #yiv4371049915ygrp-mlmsg pre, #yiv4371049915 code {font:115% monospace;}#yiv4371049915 #yiv4371049915ygrp-mlmsg * {line-height:1.22em;}#yiv4371049915 #yiv4371049915ygrp-mlmsg #yiv4371049915logo {padding-bottom:10px;}#yiv4371049915 #yiv4371049915ygrp-msg p a {font-family:Verdana;}#yiv4371049915 #yiv4371049915ygrp-msg p#yiv4371049915attach-count span {color:#1E66AE;font-weight:700;}#yiv4371049915 #yiv4371049915ygrp-reco #yiv4371049915reco-head {color:#ff7900;font-weight:700;}#yiv4371049915 #yiv4371049915ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4371049915 #yiv4371049915ygrp-sponsor #yiv4371049915ov li a {font-size:130%;text-decoration:none;}#yiv4371049915 #yiv4371049915ygrp-sponsor #yiv4371049915ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4371049915 #yiv4371049915ygrp-sponsor #yiv4371049915ov ul {margin:0;padding:0 0 0 8px;}#yiv4371049915 #yiv4371049915ygrp-text {font-family:Georgia;}#yiv4371049915 #yiv4371049915ygrp-text p {margin:0 0 1em 0;}#yiv4371049915 #yiv4371049915ygrp-text tt {font-size:120%;}#yiv4371049915 #yiv4371049915ygrp-vital ul li:last-child {border-right:none !important;}#yiv4371049915

Wed Oct 25, 2017 7:27 am (PDT) . Posted by:

jbr863

Hey Randy,
When I tried smfGetGuruFocusData("AGX", 203, "Y") recently, where Item # 203 represents Free Cash Flow, it just returned the company name. From what I can tell the URL is forming correctly so it may be something in the extract and data processing lines.


I believe that I'm seeing a similar issue with Net Net Working Capital (Item # 41). Your help in figuring out why it's not pulling the correct numerical data will be appreciated, as always.

Wed Oct 25, 2017 1:45 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Try using smfGetGuruFocusItem() instead. smfGetGuruFocusData() is an older
version of that routine and was never really mentioned other than in the
add-in change log? Or did I announce it? Where did you get the #203 and #41?

On Wed, Oct 25, 2017 at 7:27 AM, jbr863@
​...
wrote:

> When I tried smfGetGuruFocusData("AGX", 203, "Y") recently, where Item
> # 203 represents Free Cash Flow, it just returned the company name. From
> what I can tell the URL is forming correctly so it may be something in the
> extract and data processing lines.
>
> I believe that I'm seeing a similar issue with Net Net Working Capital
> (Item # 41). Your help in figuring out why it's not pulling the correct
> numerical data will be appreciated, as always.
>
>
>

Wed Oct 25, 2017 11:54 am (PDT) . Posted by:

"Dick Hurlburt" rtexican

I have attached the Fund Research workbook I am creating with the JSON data
links from your info.

I delete the symbols before I close so that it reopens faster. If I have
several in the columns for the initial data and it takes a while for it to
open.

I lack YTD, the current NAV, a field for the FUND NAME (except the
RCHGetElementNumber method, which delays the opening even more) and the
SYMBOL of the holdings fund/companies. I am still trying different JSONData
attempts to find one that might work.

I really appreciate all that you do for us. Just wanted you to see
something I have put together with your information.

Also, in YAHOO, one of the module names along with "Holdings", etc., is
"Summary". Is there a "fundSummary" in the JSON Data? Just wondering.

All the above is just FYI ...

Thanks again.

Richard

From: Dick Hurlburt [mailto:RHerb505@charter.net]
Sent: Saturday, October 21, 2017 5:58 PM
To: 'smf_addin@yahoogroups.com' <smf_addin@yahoogroups.com>
Subject: JSON Data

Randy:

I have created a Fund data workbook using the JSON Mutual Fund criteria. I
am having a bit of a problem with the topHoldings Module and was hoping you
could help me with it. I have the problem explained and exampled in the
attached.

It is interesting that the formulae really don't remain the same for all the
data extractions, but, believe I have conquered most of them.

I also note that there is no extraction for "Yield" in the Fund JSON
criteria nor the stock criteria.

Thank you for all you do. It amazes me.

Richard Hurlburt

Attachment(s) from Dick Hurlburt
1 of 1 File(s)

Wed Oct 25, 2017 1:22 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

For some things like this, I actually make it a two-step workbook. First
step is a worksheet that uses the smfUpdateDownloadTable macro. That
worksheet is used to download and hold the data. Since it loads the tables
with values instead of formulas, no recalculation is necessary when opening
the workbook. And the macro can run on selected rows, selected columns, or
a selected range within the table. I often add a "time stamp" column so I
know when I last updated the row.

Then I have a worksheet that presents the data that has been collected.
That one may or may not need to grab any data from the Internet. I may just
need to look up data from the smfUpdateDownloadTable workbook.

YTD would be something like:

=smfGetYahooJSONField("VFINX","fundPerformance","quoteSummary.result.0.fundPerformance.performanceOverview.ytdReturnPct.raw")

Not everything is in the JSON files. Some data still needs to be scraped
from the web pages.

One workbook I created recently was really fast except for ex-dividend
dates, so I made an smfUpdateDownloadTable workbook just to grab the data I
needed to determine/estimate ex-dividend dates. For example:

*Ticker* *TimeStamp* *GuruFocus Ex-Dividend Date* *Yahoo Ex-Dividend
Date* *Estimated
Ex-Dividend Date*
*WFC* 2017-10-24 11:18 2017-08-02 2016-11-02 2017-11-02
*PEP* 2017-10-24 11:18 2017-08-30 2016-11-30 2017-11-30
*INTC* 2017-10-22 01:31 2017-12-01 2016-11-03 2017-12-01
*PFE* 2017-10-22 01:31 2017-12-01 2016-11-08 2017-12-01

And, since ex-dividend dates only change once a quarter, there's no longer
a need to grab the data every time I do something. I can just look it up in
this table. And when I do an update, I really only need to update a few
rows -- the ones with older ex-dividend dates from GuruFocus -- to see if a
dividend has now been declared.

On Wed, Oct 25, 2017 at 11:54 AM, 'Dick Hurlburt' RHerb505@
​...
wrote:

>
> I have attached the Fund Research workbook I am creating with the JSON
> data links from your info.
>
>
>
> I delete the symbols before I close so that it reopens faster. If I have
> several in the columns for the initial data and it takes a while for it to
> open.
>
> I lack YTD, the current NAV, a field for the FUND NAME (except the
> RCHGetElementNumber method, which delays the opening even more) and the
> SYMBOL of the holdings fund/companies. I am still trying different
> JSONData attempts to find one that might work.
>
>
>
> I really appreciate all that you do for us. Just wanted you to see
> something I have put together with your information.
>
>
>
> Also, in YAHOO, one of the module names along with "Holdings", etc., is
> "Summary". Is there a "fundSummary" in the JSON Data? Just wondering.
>
>
>
> All the above is just FYI ...
>
>
>

Wed Oct 25, 2017 12:29 pm (PDT) . Posted by:

johnyahoo

I've been away from Excel for awhile and downloaded the latest group of files I could find in RCH_Stock_Market_Functions-2.1.2017.09.17.zip.

Can't get any version of smfGetOptionExpirations to return anything other than "None".

Tried "8","B","G","N","OX","Y" but none worked. Where are we getting option expirations from these days?

I did see the notes in the blog about OptionsExpress and Google but is any source working for expirations?

Thanks
John

Wed Oct 25, 2017 1:26 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The only time smfGetOptionQuotes() should return "None" is if you haven't
passed a ticker symbol?

I just tried each data source and got back:

*Option Ticker Symbol* *Source* *Result*
TGT 10/27 2017 $63.00 Call Y $0.06
TGT 10/27 2017 $63.00 Call B $0.06
TGT 10/27 2017 $63.00 Call G
TGT 10/27 2017 $63.00 Call 8 Error
TGT 10/27 2017 $63.00 Call OX Error
TGT 10/27 2017 $63.00 Call N $0.06

What I would have expected. "OX" returns "Error" because they no longer
exist. "8" returns "Error" because I didn't go through the process of
creating the security cookie (it's a PITA to do). "G" returned a blank
value because they have spotty options coverage.

These days, I primarily use Yahoo and BarChart. BarChart because it's
faster. However, they only update their option quotes hourly, so the data
can be very stale. Yahoo often has zero bid and ask prices for options when
the market is closed.

For my last few workbooks, I've been using Yahoo's "portfolioView" JSON
file or their option expiration JSON file. I just updated the
smfGetYahooPortfolioView() function (but haven't uploaded the changes) to
parse the text file line by line instead of doing JSON field extractions.
Much faster, and it now does hundreds of ticker symbols. Equities are real
time, but options are delayed at least 15 minutes.

A direction extraction of the above bid price from their options JSON file
would like like:

=smfStrExtr(RCHGetWebData("https://query1.finance.yahoo.com/
v7/finance/options/TGT?date=1509062400","TGT171027C00063000",400),"""
bid"":",",",1)

Going forward, I think the smfGetYahooPortfolioView() function would be the
most effective way to get the data. It's array-entered over a range, so I
set up one worksheet for data retrieval (usually the single function and a
single Internet access), then refer to that worksheet in my "presentation"
worksheet. Also, since it doesn't return anything for invalid symbols, the
output may not correspond to input tickers on a line by line basis.

On Wed, Oct 25, 2017 at 11:59 AM, jwalkergh@centurytel.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I've been away from Excel for awhile and downloaded the latest group of
> files I could find in RCH_Stock_Market_Functions-2.1.2017.09.17.zip.
>
> Can't get any version of smfGetOptionExpirations to return anything other
> than "None".
>
> Tried "8","B","G","N","OX","Y" but none worked. Where are we getting
> option expirations from these days?
>
> I did see the notes in the blog about OptionsExpress and Google but is any
> source working for expirations?
>
>

Wed Oct 25, 2017 6:01 pm (PDT) . Posted by:

johnyahoo

Thanks Randy but I was referring to Options Expirations not Options Quote.
What are you using now for smfGetOptionExpirations?
I am using a symbol of course >> e.g.smfGetOptionExpirations("SPY","B") (or any other source) returns "None" as the 1st element.

John

Wed Oct 25, 2017 1:46 pm (PDT) . Posted by:

john_hoel

I am currently using Excel 2013, with the SMF add-in and several other add-ins that I have written. Does anyone know of compatibility issues between 2013 and 365, especially any with the object models?


Wed Oct 25, 2017 2:45 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I'm currently maintaining the add-in under EXCEL 365 (upgraded from EXCEL
2010), and I've not seen issues.

I can't answer about object models, as I don't really use them.

On Wed, Oct 25, 2017 at 1:46 PM, john@
​...
wrote:

>
> I am currently using Excel 2013, with the SMF add-in and several other
> add-ins that I have written. Does anyone know of compatibility issues
> between 2013 and 365, especially any with the object models?
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 19.35

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)