8 New Messages
Digest #2317
| 2b Re: Yahoo, Price Target Summary, of the Mean, Median, High, Low stoc by "Randy Harmelink" rharmelink |
Messages
Fri Aug 17, 2012 6:01 am (PDT) . Posted by:
"mkorgie" mkorgie
I experience the slowness when retrieving about 50 tickers with 10 calls each to RCHGetYahooQuotes. I use the SMFUpdateDownloadTable macro to advance through the tickers and get the 10 pieces of information. Each ticker row is taking 3-4 seconds each to update (3-4 minutes for the entire 50 tickers).
Is it possible that Yahoo has begun to enforce a per second rate limit for queries? Perhaps they are placing an "wait delay" for IP addresses that are submitting more than x queries per second?
I have not found current rate limit documentation but something like this link:
http://developer.yahoo.com/yql/guide/usage_info_limits.html
Mike
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Well, the original message was on the RCHGetYahooQuotes() function, which
> has nothing to do with options. RCHGetYahooQuotes() gets a CSV file, with
> as much data as was requested in a single Internet access.
>
> Options are retrieved on a web page by web page basis, which will be
> slower. Even more so if there are more delays than normal in Yahoo
> response time. For each stock and each time period, you need to get another
> web page.
>
> On Thu, Aug 16, 2012 at 1:53 PM, nautilustech <fwkirk@...> wrote:
>
> > > For those of you that have delays -- what kinds of quotes are you
> > > getting: US stocks, foreign stocks, futures, currencies, delayed
> > > quotes, real-time quotes, etc.?
> >
> > U.S. stocks - current prices, ITM1, ITM2, ITM3, OTM1, option last price,
> > ask and bid. About 6 stock symbols. It's taking about 10-15 minutes,
> > sometimes a little faster.
> >
>
> On Thu, Aug 16, 2012 at 2:06 PM, kjtemplin <templin@...> wrote:
>
> > I am having similar problems with Yahoo (this is not the first time they
> > have done something to slow down the process and otherwise cause Excel to
> > run erratically). I retrieve mostly delayed US stocks, ETF's and option
> > quotes. This is very frustrating.
> >
>
Is it possible that Yahoo has begun to enforce a per second rate limit for queries? Perhaps they are placing an "wait delay" for IP addresses that are submitting more than x queries per second?
I have not found current rate limit documentation but something like this link:
http://developer.
Mike
--- In smf_addin@yahoogrou
>
> Well, the original message was on the RCHGetYahooQuotes(
> has nothing to do with options. RCHGetYahooQuotes(
> as much data as was requested in a single Internet access.
>
> Options are retrieved on a web page by web page basis, which will be
> slower. Even more so if there are more delays than normal in Yahoo
> response time. For each stock and each time period, you need to get another
> web page.
>
> On Thu, Aug 16, 2012 at 1:53 PM, nautilustech <fwkirk@...> wrote:
>
> > > For those of you that have delays -- what kinds of quotes are you
> > > getting: US stocks, foreign stocks, futures, currencies, delayed
> > > quotes, real-time quotes, etc.?
> >
> > U.S. stocks - current prices, ITM1, ITM2, ITM3, OTM1, option last price,
> > ask and bid. About 6 stock symbols. It's taking about 10-15 minutes,
> > sometimes a little faster.
> >
>
> On Thu, Aug 16, 2012 at 2:06 PM, kjtemplin <templin@...
>
> > I am having similar problems with Yahoo (this is not the first time they
> > have done something to slow down the process and otherwise cause Excel to
> > run erratically)
> > quotes. This is very frustrating.
> >
>
Fri Aug 17, 2012 9:59 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Why not change those 500 calls into a single call by using an array-entered
RCHGetYahooQuotes()?
If you want values, you could even write a short macro to fill the range.
An example:
Sub Test()
Range("A1:D2") = RCHGetYahooQuotes("MMM,IBM", "sl1d1t1", pDim1:=2,
pDim2:=4)
End Sub
On Fri, Aug 17, 2012 at 6:01 AM, mkorgie <mkorgie@southwind.net > wrote:
> I experience the slowness when retrieving about 50 tickers with 10 calls
> each to RCHGetYahooQuotes. I use the SMFUpdateDownloadTable macro to
> advance through the tickers and get the 10 pieces of information. Each
> ticker row is taking 3-4 seconds each to update (3-4 minutes for the entire
> 50 tickers).
>
> Is it possible that Yahoo has begun to enforce a per second rate limit for
> queries? Perhaps they are placing an "wait delay" for IP addresses that
> are submitting more than x queries per second?
>
> I have not found current rate limit documentation but something like this
> link:
>
> http://developer.yahoo.com/yql/guide/usage_info_limits.html
>
>
RCHGetYahooQuotes(
If you want values, you could even write a short macro to fill the range.
An example:
Sub Test()
Range("A1:D2"
pDim2:=4)
End Sub
On Fri, Aug 17, 2012 at 6:01 AM, mkorgie <mkorgie@southwind.
> I experience the slowness when retrieving about 50 tickers with 10 calls
> each to RCHGetYahooQuotes. I use the SMFUpdateDownloadTa
> advance through the tickers and get the 10 pieces of information. Each
> ticker row is taking 3-4 seconds each to update (3-4 minutes for the entire
> 50 tickers).
>
> Is it possible that Yahoo has begun to enforce a per second rate limit for
> queries? Perhaps they are placing an "wait delay" for IP addresses that
> are submitting more than x queries per second?
>
> I have not found current rate limit documentation but something like this
> link:
>
> http://developer.
>
>
Fri Aug 17, 2012 12:40 pm (PDT) . Posted by:
"mkorgie" mkorgie
Very nice Randy! ... you make me smarter everyday!
I generalized your suggestion and it's lightning fast. This way I can insert ticker rows and add/change Yahoo parameters without messing with the macro.
Where: [YahooQuoteArray] is a defined name = offset($a$1,0,0,TickerCount,YahooParameterCount)
[YahooQuoteArray] = RCHGetYahooQuotes([TickerList], [YahooParameterList], pDim1:=[TickerCount], pDim2:=[YahooParameterCount])
Best regards! Mike
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Why not change those 500 calls into a single call by using an array-entered
> RCHGetYahooQuotes()?
>
> If you want values, you could even write a short macro to fill the range.
> An example:
>
> Sub Test()
> Range("A1:D2") = RCHGetYahooQuotes("MMM,IBM", "sl1d1t1", pDim1:=2,
> pDim2:=4)
> End Sub
>
> On Fri, Aug 17, 2012 at 6:01 AM, mkorgie <mkorgie@...> wrote:
>
> > I experience the slowness when retrieving about 50 tickers with 10 calls
> > each to RCHGetYahooQuotes. I use the SMFUpdateDownloadTable macro to
> > advance through the tickers and get the 10 pieces of information. Each
> > ticker row is taking 3-4 seconds each to update (3-4 minutes for the entire
> > 50 tickers).
> >
> > Is it possible that Yahoo has begun to enforce a per second rate limit for
> > queries? Perhaps they are placing an "wait delay" for IP addresses that
> > are submitting more than x queries per second?
> >
> > I have not found current rate limit documentation but something like this
> > link:
> >
> > http://developer.yahoo.com/yql/guide/usage_info_limits.html
> >
> >
>
I generalized your suggestion and it's lightning fast. This way I can insert ticker rows and add/change Yahoo parameters without messing with the macro.
Where: [YahooQuoteArray] is a defined name = offset($a$1,
[YahooQuoteArray] = RCHGetYahooQuotes(
Best regards! Mike
--- In smf_addin@yahoogrou
>
> Why not change those 500 calls into a single call by using an array-entered
> RCHGetYahooQuotes(
>
> If you want values, you could even write a short macro to fill the range.
> An example:
>
> Sub Test()
> Range("A1:D2"
> pDim2:=4)
> End Sub
>
> On Fri, Aug 17, 2012 at 6:01 AM, mkorgie <mkorgie@...
>
> > I experience the slowness when retrieving about 50 tickers with 10 calls
> > each to RCHGetYahooQuotes. I use the SMFUpdateDownloadTa
> > advance through the tickers and get the 10 pieces of information. Each
> > ticker row is taking 3-4 seconds each to update (3-4 minutes for the entire
> > 50 tickers).
> >
> > Is it possible that Yahoo has begun to enforce a per second rate limit for
> > queries? Perhaps they are placing an "wait delay" for IP addresses that
> > are submitting more than x queries per second?
> >
> > I have not found current rate limit documentation but something like this
> > link:
> >
> > http://developer.
> >
> >
>
Fri Aug 17, 2012 5:41 pm (PDT) . Posted by:
"Vinod" vinod_24
Randy/Mike,
I new to this group.
Tried to use the array formula like
RCHGetYahooQuotes(
But is doesnt work. If I put A4:A100 then it works
How I could make of the macros you were mentioning in the message
Thanks
vinod
--- In smf_addin@yahoogrou
>
> Very nice Randy! ... you make me smarter everyday!
>
> I generalized your suggestion and it's lightning fast. This way I can insert ticker rows and add/change Yahoo parameters without messing with the macro.
>
> Where: [YahooQuoteArray] is a defined name = offset($a$1,
>
> [YahooQuoteArray] = RCHGetYahooQuotes(
>
> Best regards! Mike
>
> --- In smf_addin@yahoogrou
> >
> > Why not change those 500 calls into a single call by using an array-entered
> > RCHGetYahooQuotes(
> >
> > If you want values, you could even write a short macro to fill the range.
> > An example:
> >
> > Sub Test()
> > Range("A1:D2"
> > pDim2:=4)
> > End Sub
> >
> > On Fri, Aug 17, 2012 at 6:01 AM, mkorgie <mkorgie@> wrote:
> >
> > > I experience the slowness when retrieving about 50 tickers with 10 calls
> > > each to RCHGetYahooQuotes. I use the SMFUpdateDownloadTa
> > > advance through the tickers and get the 10 pieces of information. Each
> > > ticker row is taking 3-4 seconds each to update (3-4 minutes for the entire
> > > 50 tickers).
> > >
> > > Is it possible that Yahoo has begun to enforce a per second rate limit for
> > > queries? Perhaps they are placing an "wait delay" for IP addresses that
> > > are submitting more than x queries per second?
> > >
> > > I have not found current rate limit documentation but something like this
> > > link:
> > >
> > > http://developer.
> > >
> > >
> >
>
Fri Aug 17, 2012 6:21 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yahoo limits the returned CSV file to 200 ticker symbols. Also, I'm
assuming you wanted the second parameter to be an "l1" string -- you're
giving it a cell reference when you just use l1.
For information on creating a macro that uses the add-in, see:
http://finance.groups.yahoo.com/group/smf_addin/message/708
On Fri, Aug 17, 2012 at 4:21 PM, Vinod <vinod_24@yahoo.com > wrote:
>
> I new to this group.
> Tried to use the array formula like
> RCHGetYahooQuotes(A4:A500,l1,,NOW())
> But is doesnt work. If I put A4:A100 then it works
>
> How I could make of the macros you were mentioning in the message
>
assuming you wanted the second parameter to be an "l1" string -- you're
giving it a cell reference when you just use l1.
For information on creating a macro that uses the add-in, see:
http://finance.
On Fri, Aug 17, 2012 at 4:21 PM, Vinod <vinod_24@yahoo.
>
> I new to this group.
> Tried to use the array formula like
> RCHGetYahooQuotes(
> But is doesnt work. If I put A4:A100 then it works
>
> How I could make of the macros you were mentioning in the message
>
Sat Aug 18, 2012 1:07 am (PDT) . Posted by:
"Bob" rkdmon
Over the past few days I tried variations of: number of ticker updates, markets USD/EUR/CHF etc and thru Friday evening it was still problematic as reported - very slow updating and Excel temporary hangs which resolved only once calc completed.
However, as of Saturday morning European time things *appear* to be back to normal for me. Fairly fast updates even with the rather complex multi-tab workbooks that I use. Test files with fewer links work like lightning again. Maybe Yahoo changed something back but so far so good...
Whilst I'm posting, I'd also like to add my thanks to Randy for all his work on this topic. The addin functions are incredibly useful and make monitoring my trading plans for each security very much better. Kudos and thanks!
Bob
--- In smf_addin@yahoogroups.com , "Heath" <heath.winter@...> wrote:
>
> Hi,
>
> I looked for information on this error in previous posts, but didn't see anything. An excel spreadsheet is freezing intermittently. Windows Resource Monitor shows that Excel is waiting for a thread to finish network I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.
>
> I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test, but the error persists. Is there another function that could be trying to send or receive data from q1.finance.vip.ac4.yahoo.com?
>
> Thanks!
>
However, as of Saturday morning European time things *appear* to be back to normal for me. Fairly fast updates even with the rather complex multi-tab workbooks that I use. Test files with fewer links work like lightning again. Maybe Yahoo changed something back but so far so good...
Whilst I'm posting, I'd also like to add my thanks to Randy for all his work on this topic. The addin functions are incredibly useful and make monitoring my trading plans for each security very much better. Kudos and thanks!
Bob
--- In smf_addin@yahoogrou
>
> Hi,
>
> I looked for information on this error in previous posts, but didn't see anything. An excel spreadsheet is freezing intermittently. Windows Resource Monitor shows that Excel is waiting for a thread to finish network I/O. The thread is a connection to q1.finance.vip.
>
> I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test, but the error persists. Is there another function that could be trying to send or receive data from q1.finance.vip.
>
> Thanks!
>
Fri Aug 17, 2012 11:07 am (PDT) . Posted by:
"rr76012" rr76012
re: Yahoo Analyst Opinion
Hello Randy,
I have looked, but I may have overlooked rchgetelementnumber numbers for the Yahoo Analyst Opinions on Price Target Summary, are there rchgetelementnumbers for these fields? (target stock prices fields the mean, median, high, low prices?
If not can you make a recommendation to download this info?
I copied and pasted and example from yahoo Analyst Opinion web page.
from Yahoo,
Price Target Summary:
Mean Target: 39.06
Median Target: 39.50
High Target: 43.00
Low Target: 35.00
No. of Brokers: 16
Thanks, have a great day.
rr76012
Hello Randy,
I have looked, but I may have overlooked rchgetelementnumber numbers for the Yahoo Analyst Opinions on Price Target Summary, are there rchgetelementnumber
If not can you make a recommendation to download this info?
I copied and pasted and example from yahoo Analyst Opinion web page.
from Yahoo,
Price Target Summary:
Mean Target: 39.06
Median Target: 39.50
High Target: 43.00
Low Target: 35.00
No. of Brokers: 16
Thanks, have a great day.
rr76012
Fri Aug 17, 2012 11:22 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
That table and those items are probably new since I did the element
numbers. Actually, it looks like there are no elements from that page. I
wonder if the entire page is new.
In any case, you can just use the RCHGetTableCell() function for the data.
For example:
=RCHGetTableCell("http://finance.yahoo.com/q/ao?s=MMM ",1,">Price Target
Summary","Mean Target")
On Fri, Aug 17, 2012 at 11:07 AM, rr76012 <rr76012@yahoo.com > wrote:
> re: Yahoo Analyst Opinion
>
> Hello Randy,
>
> I have looked, but I may have overlooked rchgetelementnumber numbers for
> the Yahoo Analyst Opinions on Price Target Summary, are there
> rchgetelementnumbers for these fields? (target stock prices fields the
> mean, median, high, low prices?
>
> If not can you make a recommendation to download this info?
>
> I copied and pasted and example from yahoo Analyst Opinion web page.
>
> from Yahoo,
>
> Price Target Summary:
> Mean Target: 39.06
> Median Target: 39.50
> High Target: 43.00
> Low Target: 35.00
> No. of Brokers: 16
>
numbers. Actually, it looks like there are no elements from that page. I
wonder if the entire page is new.
In any case, you can just use the RCHGetTableCell(
For example:
=RCHGetTableCell(
Summary","Mean Target")
On Fri, Aug 17, 2012 at 11:07 AM, rr76012 <rr76012@yahoo.
> re: Yahoo Analyst Opinion
>
> Hello Randy,
>
> I have looked, but I may have overlooked rchgetelementnumber numbers for
> the Yahoo Analyst Opinions on Price Target Summary, are there
> rchgetelementnumber
> mean, median, high, low prices?
>
> If not can you make a recommendation to download this info?
>
> I copied and pasted and example from yahoo Analyst Opinion web page.
>
> from Yahoo,
>
> Price Target Summary:
> Mean Target: 39.06
> Median Target: 39.50
> High Target: 43.00
> Low Target: 35.00
> No. of Brokers: 16
>
Tidak ada komentar:
Posting Komentar