Jumat, 07 Juli 2017

[smf_addin] Digest Number 4083[1 Attachment]

15 Messages

Digest #4083
1b
3c
Re: RCHgetelements still obsolete? by "Randy Harmelink" rharmelink
4e
Re: RCHGetYahooQuotes returning "Error" by "Paul D Pochan" paul_d_pochan
4f
Re: RCHGetYahooQuotes returning "Error" by "Randy Harmelink" rharmelink
4g
Re: RCHGetYahooQuotes returning "Error" by "Randy Harmelink" rharmelink
5b
Re: Array Entering Function Calls by "Randy Harmelink" rharmelink

Messages

Thu Jul 6, 2017 10:43 pm (PDT) . Posted by:

another_investor

For MTL (with the workbook you created as an example on the smfGetYahooHistory function), when I enter an end date up to and including the 2nd July then I see the full array (tab '2nd July' on the enclosed WB).



Once the end date exceeds the 2nd July, then the last full row I see is the 3rd July, after which I only see the date of the 30th June.

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

Thu Jul 6, 2017 11:31 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Thu, Jul 6, 2017 at 10:43 PM, steviejacobs@
​...
wrote:

>
> For MTL (with the workbook you created as an example on the
> smfGetYahooHistory function), when I enter an end date up to and including
> the 2nd July then I see the full array (tab '2nd July' on the enclosed WB).
>
​
In your first tab, I see:

Ticker: MTL *Date* *Open* *High* *Low* *Close* *Volume* *Unadj*
Start date: 2017-01-01 2017-06-29 $4.7400 $4.7400 $4.5000 $4.6000 190400
$4.6000
End date: 2017-07-02 2017-06-28 $4.6200 $4.7700 $4.5700 $4.7000 114800
$4.7000
Period: d 2017-06-27 $4.5500 $4.6400 $4.5300 $4.5600 107800 $4.5600
Items: dohlcvufgx 2017-06-26 $4.6300 $4.6300 $4.4300 $4.5300 126900
$4.5300
Names: 1 2017-06-23 $4.5000 $4.6000 $4.4700 $4.5900 199400 $4.5900
Resort: 0 2017-06-22 $4.4700 $4.5200 $4.3300 $4.4500 192000 $4.4500
2017-06-21 $4.2700 $4.3200 $4.1700 $4.2200 123400 $4.2200
​
​​That's what Yahoo returns for that date combination. For example:

https://finance.yahoo.com/quote/MTL/history?period1=
1483228800&period2=1499040000&interval=1d&filter=history&frequency=1d

​No idea why Yahoo drops the 6/30 data. Maybe because the data is bad (null
values)?​

Once the end date exceeds the 2nd July, then the last full row I see is the
> 3rd July, after which I only see the date of the 30th June.
>

​Not what I'm seeing? In your second tab, I see:

Ticker: MTL *Date* *Open* *High* *Low* *Close* *Volume* *Unadj*
Start date: 2017-01-01 2017-07-06 $4.7900 $4.8500 $4.7200 $4.7800 53526
$4.7800
End date: 2017-07-06 2017-07-03 $4.6500 $4.7400 $4.6400 $4.7200 53000
$4.7200
Period: d 2017-06-30 $4.6000 $4.6000 $4.6000 $4.6000 0 $4.6000
Items: dohlcvufgx 2017-06-29 $4.7400 $4.7400 $4.5000 $4.6000 190400
$4.6000
Names: 1 2017-06-28 $4.6200 $4.7700 $4.5700 $4.7000 114800 $4.7000
Resort: 0 2017-06-27 $4.5500 $4.6400 $4.5300 $4.5600 107800 $4.5600
2017-06-26 $4.6300 $4.6300 $4.4300 $4.5300 126900 $4.5300
​

Fri Jul 7, 2017 7:00 am (PDT) . Posted by:

tonyestep

Today the Yahoo historical prices are correctly updated for some series (QQQ, for example) but not others (SPY, for example). This is true whether you get the prices via the SMF function or simply go to the Yahoo Finance website and fetch them by hand.


Meanwhile, the Google historical prices are correct. I believe that Google doesn't allow large automated hits to their data, but perhaps there could be an SMF function to grab some Google historical data that could save the day when Yahoo fails. I spent some anxious moments this morning manually patching a bunch of data......


Tony Estep

Fri Jul 7, 2017 7:49 am (PDT) . Posted by:

lapub1

Hi Randy

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



Also Error for 583 RCHGetElementNumber
Error for RCHGettableCell


#NAME for smfGetYahooJSONField


Thanks,

Fri Jul 7, 2017 10:56 am (PDT) . Posted by:

lapub1

Hi Randy,

"RCHGETyahooquotes" for float, and short ratio works, however I still get Error for "number of analysts" when using "RCHGetElementNumber"


Thanks,
Elliot.

Fri Jul 7, 2017 4:06 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Since you have an old version of the add-in, it may be tough for us to sync
up results...

You may need this setting for your Internet options for the original
functions to work:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/10464

What do you get with:

=RCHGetTableCell("https://finance.yahoo.com/quote/MMM/analysts",3,">No. of
analysts")
=RCHGetTableCell("https://finance.yahoo.com/quote/MMM/analysts",0,">No. of
analysts")
=RCHGetWebData("https://finance.yahoo.com/quote/MMM/analysts","No. of
Analysts",150)

On Fri, Jul 7, 2017 at 10:56 AM, lapub1@
​...
wrote:

>
> "RCHGETyahooquotes" for float, and short ratio works, however I still get
> Error for "number of analysts" when using "RCHGetElementNumber"
>
>

Fri Jul 7, 2017 9:55 am (PDT) . Posted by:

e_bartsch

Randy:


For what it is worth, I'm getting the same error, too. It works intermittently. Seems like it is more problematic during the trading day.


Is there a substitute formula that carries out the same function? RCHGetYahooQuotes is returning errors for the last price ("l1") and the name of the security ("n").


I'm also having problems with smfGetOptionStrikes. That, too, works intermittently. I played around with it by changing the server for the call and the manner in which the call is made. That seemed to fix it earlier in the week. Today, however, I'm getting a blank value returned.


Other RCH & smf formulas on the spreadsheet are working.


Thanks for the help.

Fri Jul 7, 2017 2:01 pm (PDT) . Posted by:

justy_sports

It is definitely a Yahoo issue. Seems to bomb out after continued querying, and won't return until I've walked away for a certain amount of time. I wonder if Yahoo is throttling site visits...

As far as I can tell, definitely no add-in issue.




Fri Jul 7, 2017 2:14 pm (PDT) . Posted by:

justy_sports

A bit more info...

I tried accessing https://download.finance.yahoo.com/d/quotes.csv?s=aapl https://download.finance.yahoo.com/d/quotes.csv?s=aapl via my web browser and received the following error.


Sorry, Unable to process request at this time -- error 999.



Below explanation was found online -


Background: This error appears to be a "catch-all" error code that Yahoo serves up when it doesn't have a more specific error code. It essentially means "Oops! Something went wrong but we don't know what, so we'll just say that Error 999 occurred."


Turn off your modem for a minute or two.
The most common reason for receiving Yahoo Error 999 is due to some sort of bandwidth limiting system that Yahoo has put in place on their servers. Once you have exceeded your allotted bandwidth for a specific period of time Yahoo gives you this Error 999 message and doesn't allow you to access the service. People have primarily reported receiving this error when they try to access Yahoo Mail or Yahoo Groups, but other Yahoo services may also be affected.


There are many programs around that offer to automate access to various Yahoo services, i.e. check your Yahoo mailbox every 5 minutes, archive Yahoo Groups messages, download files from the Yahoo Groups Photos and Files sections, etc. If you use one of these automated tools then there is a very real possibility that you will run into the Error 999 message. Normal human usage of the Yahoo services shouldn't normally generate enough traffic to trigger the Error 999 message unless you're a very heavy user.Why has Yahoo done this?


There are two reasons that I can think of:
To prevent DoS (Denial of Service) attacks.
To stop automated tasks from hammering their servers with hundreds of requests a second.


It appears that Yahoo uses your IP address to track the amount of traffic you're generating on Yahoo, and once you reach the limit you get blocked by the Unable to process request at this time -- error 999 message. Once triggered you will find that your IP address has been blocked for a period of time, somewhere between 2 and 24 hours usually.


I have used the same workbook for years with no issues at all... my guess is Yahoo might have recently changed their traffic threshold.




Fri Jul 7, 2017 2:30 pm (PDT) . Posted by:

e_bartsch

Yeah, that is an unfortunate problem. Is there a substitute source for the information or a workaround? For example, figure out a way to build backwards into the data using a different resource, using a formula technique similar to this approach (here, it pulls the 14 Day RSI with an HTML call and read):


='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfConvertData('C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetWebData("https://www.barchart.com/stocks/quotes/" & C31 & "/cheat-sheet","14 Day RSI at 30%",200),"""rawValue"":","}"))


This is out of my league. But, Randy may have some ideas. Looks like Yahoo is shutting people out.

Fri Jul 7, 2017 3:38 pm (PDT) . Posted by:

"Paul D Pochan" paul_d_pochan

All,My issue. I have 2 options on my spreadsheet. The option quote updates for one and the other gives ERROR.
Regards

Sent from Yahoo Mail on Android

Fri Jul 7, 2017 3:53 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The best work-around is to do as few RCHGetYahooQuotes() functions as
possible. That means up to 200 ticker symbols per invocation, getting all
of the data items you need. I've sometimes done it in a separate worksheet,
and then referred to data items in that worksheet when I want to present
the data differently.

Two advantages to doing so:

1. The number of individual errors is far less. For 200 ticker symbols and
5 data items for each, you will get at most one Internet errors, versus the
other having opportunities for errors on any of 1000 Internet requests.

2. Recalculation would be much faster. So, if there is an error, easy to
recalculate it.

smfGetOptionStrikes() would depend on the data source. If Yahoo is
"throttling" by IP address, those functions using Yahoo would also be
affected. But reducing the number of request to Yahoo by array-entering the
RCHGetYahooQuotes() function could reduce these errors as well.

On Fri, Jul 7, 2017 at 9:55 AM, eabartsch@
​...
wrote:

> For what it is worth, I'm getting the same error, too. It works
> intermittently. Seems like it is more problematic during the trading day.
>
> Is there a substitute formula that carries out the same function?
> RCHGetYahooQuotes is returning errors for the last price ("l1") and the
> name of the security ("n").
>
> I'm also having problems with smfGetOptionStrikes. That, too, works
> intermittently. I played around with it by changing the server for the
> call and the manner in which the call is made. That seemed to fix it
> earlier in the week. Today, however, I'm getting a blank value returned.
>
> Other RCH & smf formulas on the spreadsheet are working.
>
>
>

Fri Jul 7, 2017 3:55 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Can't help without more details. I need at least an example of the formula
causing an error. Best to shut down EXCEL, create a new workbook, and try
it there first. If it still gets an error, then send it along so I can try
an figure out what happened to it.

Also, add-in version information would be helpful:

=RCHGetElementNumber("Version")

On Fri, Jul 7, 2017 at 11:38 AM, Paul D Pochan paul_d_pochan@
​...
wrote:

>
> My issue. I have 2 options on my spreadsheet. The option quote updates for
> one and the other gives ERROR.
>
>

Fri Jul 7, 2017 3:28 pm (PDT) . Posted by:

jdsboston

Hi Randy - I'm another major supporter of your add-in. Many thanks for creating and maintaining it!


I have a huge spreadsheet with hundreds of single-cell calls, and it's stopped working reliably (returning "Error" in many cases). So I'm trying to convert it to use array formulas, but having mixed results.


These formulas work:
=RCHGetYahooQuotes(A6:A14,"n") - returning company names for the list of tickers in A6:A14

=RCHGetYahooQuotes(A6:A14,"l1",,NOW()) - for latest price of each stock

=RCHGetYahooQuotes(A6:A14,"p2") - for last day's percent price change for each stock



But these aren't working:
=RCHGetElementNumber(A6:A14, 989) - returns #VALUE in all rows

=RCHGetElementNumber(A6:A14, 990) - same

=smfPricesByDates(A6:I14,G6) - returns #VALUE, not prices for date in G6 (also doesn't work if G6:G14 is an array of dates)

=smfGetYahooHistory(A6:A14,M6,M6,"d","c",0,0) - returns #VALUE (also doesn't work with an array of dates)



Can all these functions be array entered?


Thanks,


Jim Schantz

Fri Jul 7, 2017 3:37 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The ones that aren't working can't be passed a range of multiple tickers.
There would be no advantage in doing so, because the add-in would still
need to go to the Internet and grab data for each individual ticker.

The reason RCHGetYahooQuotes() has an advantage when array-entered is
because Yahoo provides a single CSV file for multiple data items for up to
200 ticker symbols with a single Internet request. So thousands of data
items can be requested in one Internet access.

Can't do multiple pairs of dates with smfGetYahooHistory() either, as it
needs to do a request for a singe pair of dates. That's why I wrote
smfPricesByDates() -- it calls smfGetYahooHistory() to get all the data it
needs and then extracts data for the requested dates.

On Fri, Jul 7, 2017 at 3:28 PM, jschantz@
​...
wrote:

> I have a huge spreadsheet with hundreds of single-cell calls, and it's
> stopped working reliably (returning "Error" in many cases). So I'm trying
> to convert it to use array formulas, but having mixed results.
>
> These formulas work:
>
> =RCHGetYahooQuotes(A6:A14,"n") - returning company names for the list of
> tickers in A6:A14
>
> =RCHGetYahooQuotes(A6:A14,"l1",,NOW()) - for latest price of each stock
>
> =RCHGetYahooQuotes(A6:A14,"p2") - for last day's percent price change for
> each stock
>
> But these aren't working:
>
> =RCHGetElementNumber(A6:A14, 989) - returns #VALUE in all rows
>
> =RCHGetElementNumber(A6:A14, 990) - same
>
> =smfPricesByDates(A6:I14,G6) - returns #VALUE, not prices for date in G6
> (also doesn't work if G6:G14 is an array of dates)
>
> =smfGetYahooHistory(A6:A14,M6,M6,"d","c",0,0) - returns #VALUE (also
> doesn't work with an array of dates)
>
> Can all these functions be array entered?
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 16.06

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)