Rabu, 26 April 2017

[smf_addin] Digest Number 3985[1 Attachment]

15 Messages

Digest #3985
1b
Re: RCHGetTableCell funtion not working by "Randy Harmelink" rharmelink
2a
2b
Re: Element #960 by "Randy Harmelink" rharmelink
2c
Re: Element #960 by "Randy Harmelink" rharmelink
2d
Re: Element #960 by "Randy Harmelink" rharmelink
2e
2f
2g
Re: Element #960 by "Randy Harmelink" rharmelink
3a
Re: RCHGetElementNumber issues by "Randy Harmelink" rharmelink
4a
Re: SMFGetOptionQuotes by "Randy Harmelink" rharmelink
5a
Re: Closing price from source other than Yahoo by "Randy Harmelink" rharmelink
6.1
7a
Re: Thanks Randy by bamzoomalice

Messages

Wed Apr 26, 2017 1:43 pm (PDT) . Posted by:

philgiz_2000

Thanks for your input Randy.. However, I don't quite understand what I can do to correct. The table function I use pulls the 1 month, 3 month and 6 month performance history. An example of a formula is:
= RCHGetTableCell("http://finance.yahoo.com/q/pm?s="&$A6&"+Performance",1,"1-Month")

where "&$A6&" pulls the data in for a stock listed in cell A6.


If you could provide the formula for this example I should be able to use as a guide.


Thanks for your help;
Phil

Wed Apr 26, 2017 4:32 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The table function will no longer work as the web page they display no
longer contains an HTML table is the delivered source code. Instead, they
build the web page from data they retrieve using a JSON call. So the data
needs to be retrieved from the JSON file.

I don't see anything on the website for a 6-month return, but 1-month and
3-month would be:

=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"?modules=fundPerformance"),2,"""oneMonth"":"),"""raw"":",","))

=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"?modules=fundPerformance"),2,"""threeMonth"":"),"""raw"":",","))

If you grab the newest version of the add-in (as of 2017-04-26), you could
instead use the new experimental function:

=smfGetYahooJSONData("VWEAX","fundPerformance","trailingReturns.oneMonth")
=smfGetYahooJSONData("VWEAX","fundPerformance","trailingReturns.threeMonth")

On Wed, Apr 26, 2017 at 1:43 PM, philgiz@
​...wrote:

>
> Thanks for your input Randy.. However, I don't quite understand what I
> can do to correct. The table function I use pulls the 1 month, 3 month and
> 6 month performance history. An example of a formula is:
> =
> RCHGetTableCell("http://finance.yahoo.com/q/pm?s="&$
> A6&"+Performance",1,"1-Month")
> where "&$A6&" pulls the data in for a stock listed in cell A6.
>
> If you could provide the formula for this example I should be able to use
> as a guide.
>
>

Wed Apr 26, 2017 1:50 pm (PDT) . Posted by:

ghart21

Many more Yahoo and YahooKS elements have gone to JSON Heaven
today (4/26/17)
++++++++++++++++++++++++++++

-----Original Message-----
From: "elandry@bmlcapital.com [smf_addin]"
Sent: Apr 26, 2017 3:21 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Element #960




Hi,




Is anybody having a problem with element #960? Seems to return
"Error" for every symbol I try. I did the force recalc, to no
avail.




Thanks


Wed Apr 26, 2017 2:40 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

This is working fine for me:

=RCHGetElementNumber("MMM";,960)

On Wed, Apr 26, 2017 at 12:21 PM, elandry@
​...wrote:

> Is anybody having a problem with element #960? Seems to return "Error" for
> every symbol I try. I did the force recalc, to no avail.
>
>

Wed Apr 26, 2017 2:41 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just tried the Key Statistics template from the web site:

RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls

...and everything appeared to work OK. Unless they are "rolling out" an
update.

On Wed, Apr 26, 2017 at 12:56 PM, ghart23@
​...wrote:

>
> Many more Yahoo and YahooKS elements have gone to JSON Heaven today
> (4/26/17)
>
>
>

Wed Apr 26, 2017 2:47 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I take that back...I just deleted all my cached files and now get no data
on that workbook.

On Wed, Apr 26, 2017 at 2:41 PM, Randy Harmelink wrote:

> I just tried the Key Statistics template from the web site:
>
> RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
>
> ...and everything appeared to work OK. Unless they are "rolling out" an
> update.
>
> On Wed, Apr 26, 2017 at 12:56 PM, ghart23@
> ​...wrote:
>
>>
>> Many more Yahoo and YahooKS elements have gone to JSON Heaven today
>> (4/26/17)
>>
>>
>>
>

Wed Apr 26, 2017 3:09 pm (PDT) . Posted by:

devropr


RCHGetElementNumber stopped for me too.

Wed Apr 26, 2017 3:40 pm (PDT) . Posted by:

lapub1

Hey Randy

I'm using SMF-update-download-table, and re-installed the add in, and tried smf force recalculate multiple times and still get error for most YHOO elements.


Anyone out there know a good replacement for Forward,Trailing P/E, and High, Low earnings estimates for current quarter? I tried switching to IBD but the numbers weren't accurate.

Wed Apr 26, 2017 3:59 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Updates to element definitions on the way. Yahoo changed the Key Statistics
web page from their URL from the "http://" protocol to "https://".

On Wed, Apr 26, 2017 at 3:23 PM, lapub1@ wrote:

>
> I'm using SMF-update-download-table, and re-installed the add in, and
> tried smf force recalculate multiple times and still get error for most
> YHOO elements.
>
> Anyone out there know a good replacement for Forward,Trailing P/E, and
> High, Low earnings estimates for current quarter? I tried switching to IBD
> but the numbers weren't accurate.
>
>

Wed Apr 26, 2017 2:33 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

When you start up EXCEL, the VBA area is initialized. It has 1000 slots for
saving web pages -- because the add-in saves the web page before it
extracts the data. That way, if you get 10 different extractions off the
web page, it only needs to retrieve the web page once. That process and
limit has been there for years.

So, if you use three different workbooks, each web page you access between
the three workbooks would use up another slot. If you leave EXCEL open, but
close a workbook and open a new workbook, the used storage slots would
remain used.

The only ways to purge the 1000 slots and start over are to either exit and
restart EXCEL (which re-initializes the VBA project), or to run the
smfForceRecalculation macro (which erases all of the saved web pages). If
you run smfForceRecalculation, EXCEL would need to re-retrieve all of the
web pages for add-in formulas in all open workbooks. It's basically a quick
way to "refresh" and recalculate all add-in functions in all open
workbooks.

You should NOT be using the add-in to build a database to screen thousands
of stocks. It's an unfair usage of the free data sources, and gives them
incentive to change their web pages so something like the add-in cannot
access the data. You should screen with another product and then use the
add-in to get more detailed data on a much smaller group of stocks that
remain.

Instead of copy and pasting values, I would suggest using the
smfUpdateDownloadTable process. It updates a table defined by tickers down
the rows and formulas across the columns, and places values into the table.
You can select a group of rows to update, leaving all the other rows alone.
I usually add a timestamp column so I can sort the table and see when the
last time I updated each ticker was.

You can also use the Zack's screener (
https://www.zacks.com/screening/stock-screener). For example, I can select
all non-OTC stocks (leaving about 4000 stocks) and get a CSV file with next
earning dates on it, such as:

Company Name Ticker Next EPS Report Date Last Close Exchange
MVC Capital, Inc. MVC 20170904 8.94 NYSE
Taylor Devices, Inc. TAYD 20170904 13.44 NSDQ
Briggs & Stratton Corporation BGG 20170817 25.05 NYSE
Alere Inc. ALR 20170816 49.12 NYSE
Cree, Inc. CREE 20170815 24.82 NSDQ
Flexsteel Industries, Inc. FLXS 20170815 53.53 NSDQ
It can be a quick way to get a lot of data. You can add a lot of fields to
the export file. Just save the screen and it's easily retrieved the next
time you go there.

As far as using 23 RCHGetElementNumber() functions for 60 stocks, that's
not 1380 web pages retrieved. If all 23 RCHGetElementNumber() functions
were retrieved from a single web page for each of the 60 stocks, it would
only need to retrieve 60 web pages. You can turn on the log (using the
right mouse context menu) before you start retrieving data and then view
the log when you're done, and you should be able to review the web pages
you've actually retrieved. For example:

2017-04-26 09:07:34 1.0313 http://finance.yahoo.com/q/ae?s=MMM
2017-04-26 09:12:37 0.2070
https://ichart.finance.yahoo.com/table.csv?s=MMM&a=0&b=1&c=2016&g=d&ignore=.csv
2017-04-26 09:13:12 0.6875
https://ichart.finance.yahoo.com/table.csv?s=MMM&a=5&b=1&c=2006&d=6&e=10&f=2006&g=d&ignore=.csv
2017-04-26 09:13:17 0.1445
https://ichart.finance.yahoo.com/table.csv?s=IBM&a=5&b=1&c=2006&d=6&e=10&f=2006&g=d&ignore=.csv
2017-04-26 09:14:52 0.9844 http://finance.yahoo.com/q?s=MMM
2017-04-26 09:16:30 1.0703 http://finance.yahoo.com/q?s=SPY
2017-04-26 09:16:31 0.8242 http://finance.yahoo.com/q?s=VFINX
2017-04-26 09:23:29 0.8516
http://finance.yahoo.com/quote/OTEX170519C00035000
2017-04-26 09:23:42 0.9492
http://finance.yahoo.com/quote/CHUY170519P02750000
2017-04-26 09:26:45 1.2695
http://finance.yahoo.com/quote/CHUY170519P00027500

​That's a timestamp, the amount of time necessary to retrieve the web page,
and the URL that was used to retrieve the web page.​ The log can be handy
when things are really slow, as you can see where web page retrievals are
taking time. I had one process that was taking forever, and the log let me
know a particular site was timing out (taking about 2 minutes to retrieve
each page). Turns out the web site was down.

On Wed, Apr 26, 2017 at 10:55 AM, defelradar@
​...wrote:

>
> what does in a session mean? does that mean between excel saves? between
> file open and close? is this what the download table is? no I am not
> building a database but what I do have is a sheet where I screen stocks
> daily based on earnings release dates and I have over 2k rows of screened
> stocks. I do not leave the formulas in all 2k rows I typically copy and
> paste values (eliminating the formulas and calculations) for any that are
> older than a day. This might leave me with 10-100 stocks per day with
> active formulas on that sheet. That does not include the other sheets in
> my workbook that also have SMF formulas in them.
>
> For example today I have 60 stocks in my screen. For that particular
> screen I use the RCFGetElementNumber function 23 times in 5 cell
> calculations. So in that case 60 *23 = 1380. I've used this add in since
> 2006 and never had issues like this and I've had those calculations I
> referenced above in place for well over 3 years. Did something change to
> limit to web page accesses?
>
>
>

Wed Apr 26, 2017 3:39 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Ah, it looks like another culprit of the "https://" change in protocol.
Working on the update now.

On Wed, Apr 26, 2017 at 10:54 AM, dharless@
​...wrote:

>
> I am seeing some intermittent behavior with this function as well though i
> am thinking it might be a Yahoo issue.
>
> I have a spreadsheet that pulls option quotes for several stocks for dates
> in 2017, 2018, and 2019. Two days it was working fine after installing
> your latest update. Today only the 2018 Option quotes are working. All of
> the 2017 and 2019 option functions are throwing an error.
>
> I will keep testing it.
>
> Thanks again for all you do for the community.
>
>

Wed Apr 26, 2017 3:46 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

More details about what you tried, please...

On Wed, Apr 26, 2017 at 1:30 PM, andrewoskoui@
​...wrote:

>
> I tried this method with the smfGetCSVFile() function and array entering
> the output over a large grid of cells, but I still only see the headers
> with no data.
>
> Is there no other way of getting price quotes for Japanese stocks?
>
>

Wed Apr 26, 2017 4:11 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

*Massive Yahoo changes*These should fix the recent issues with the Yahoo
Key Statistics web pages and retrieval of options quotes from Yahoo using
smfGetOptionQuotes(). Plus other elements marked as obsolete or TBD.

*Date* *Routine* *Description*
2017-04-26 RCHGetElementNumber Marked elements #4930-5195 and #4669-4670
(Yahoo mutual fund data) on smf-elements-2.txt as "TBD" until things can be
changed to JSON extractions
2017-04-26 RCHGetElementNumber Obsoleted elements #13822-13861 and
#13901-13920 on smf-elements-2.txt because Yahoo dropped their
advances/declines web page
2017-04-26 smfGetOptionQuotes
smfGetYahooOptionQuotes
RCHGetElementNumber Updated all remaining Yahoo usage of protocol "http://"
to "https://", both in element definitions and in the XLA file.
2017-04-24 smfGetJSONData New *experimental function* to extract data from
Yahoo JSON files
2017-04-18 RCHGetElementNumber

- Updated elements #609 to #612 (Current year estimates from Yahoo) on
smf-elements-2.txt, for label changes on web page
- Obsoleted elements #625 to #632 (Current P/E and PEG ratios from
Yahoo) on smf-elements-2.txt, dropped from web page

​Check these website pages for updates:

-- http://ogres-crypt.com/SMF/
-- http://ogres-crypt.com/SMF/Documentation/​
-- http://ogres-crypt.com/SMF/Elements/

New/updated files:

-- RCH_Stock_Market_Functions-2.1.2017.04.26.zip​

-- RCHGetElementNumber-Element-Definitions.xls
-- smf-elements-2.txt

-- ​Change-Log.html
-- smfGetYahooJSONData-Function.html

Best practice is to close EXCEL before updating file in the SMF folder.

Wed Apr 26, 2017 6:37 pm (PDT) . Posted by:

sjagers

You're the greatest, Randy. Thanks. I hate Yahoo. They need to quit changing their website.

Wed Apr 26, 2017 7:02 pm (PDT) . Posted by:

bamzoomalice

I must have had an old alias I cannot remember! Attached is an early email I found dating to 1/7/2007.


Cheers!




Attachment(s) from
1 of 1 File(s)
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Posting Komentar