4 Messages
Digest #3816
Messages
Mon Oct 10, 2016 6:30 pm (PDT) . Posted by:
beatnut8827
Hi
Is there a way of Speeding up yahoo data retrieval? When I select update it takes about 3 or 4 minutes to update the spreadsheet. Is it a function of processor speed or memory? Or distance - I live in Australia. Or is it just what it is?
Neil
Is there a way of Speeding up yahoo data retrieval? When I select update it takes about 3 or 4 minutes to update the spreadsheet. Is it a function of processor speed or memory? Or distance - I live in Australia. Or is it just what it is?
Neil
Mon Oct 10, 2016 7:34 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
In general, the three primary sources of a slow update:
1. The number of different web pages being retrieved.
2. The size of the web pages being retrieved (AdvFN web pages are huge).
3. The speed of the website the data is being retrieved from (slower sites
include OptionsXpress),
The technique that has helped a lot of people is to array-enter the
RCHGetYahooQuotes() function so that they have only one for each set of 200
ticker symbols. If they used 100 individual RCHGetYahooQuotes() to get the
last price for 100 tickers, they'd be going to the Internet 100 times. If
they array-entered a single RCHGetYahooQuotes() for those 100 tickers,
they'd be going to the Internet once. And if they array-entered additional
columns of data for those 100 tickers, it would still be going to the
Internet once. That's because it's one of the few data sources that returns
data as a customized CSV file, for each data request.
Another technique would be to avoid going to individual web pages if a set
of data items is all on the same page. For example, if you're retrieving
balance sheet items, don't get one line item from Yahoo, one from AdvFN,
and one from GuruFocus. Getting them from different sources could be a
problem anyway, because they may not all be presenting data with the same
fiscal dates.
If you right-click the mouse in your worksheet to bring up the context
menu, you can turn on a logging feature, and use the same menu to open the
log as a CSV file. I usually turn the log on every time I start up EXCEL.
Last weekend, I was getting really slow update times -- reviewed the log
and found out each of my four OptionsXPress extractions took between 59 and
119 seconds. So they were exceptionally slow for some reason. It's also
handy to see how many web pages you're actually retrieving.
Apart from that general information, I'd need more specifics about what
you're retrieving.
However, since you're specifically asking about Yahoo, which is typically
very quick, I suspect it's either having a lot of individual
RCHGetYahooQuotes() functions, or something like retrieving "special" items
for a large number of ticker symbols (e.g. something from the "Key
Statistics" page).
On Mon, Oct 10, 2016 at 6:30 PM, neil@
...wrote:
> Is there a way of Speeding up yahoo data retrieval? When I select update
> it takes about 3 or 4 minutes to update the spreadsheet. Is it a function
> of processor speed or memory? Or distance - I live in Australia. Or is it
> just what it is?
>
>
1. The number of different web pages being retrieved.
2. The size of the web pages being retrieved (AdvFN web pages are huge).
3. The speed of the website the data is being retrieved from (slower sites
include OptionsXpress)
The technique that has helped a lot of people is to array-enter the
RCHGetYahooQuotes(
ticker symbols. If they used 100 individual RCHGetYahooQuotes(
last price for 100 tickers, they'd be going to the Internet 100 times. If
they array-entered a single RCHGetYahooQuotes(
they'd be going to the Internet once. And if they array-entered additional
columns of data for those 100 tickers, it would still be going to the
Internet once. That's because it's one of the few data sources that returns
data as a customized CSV file, for each data request.
Another technique would be to avoid going to individual web pages if a set
of data items is all on the same page. For example, if you're retrieving
balance sheet items, don't get one line item from Yahoo, one from AdvFN,
and one from GuruFocus. Getting them from different sources could be a
problem anyway, because they may not all be presenting data with the same
fiscal dates.
If you right-click the mouse in your worksheet to bring up the context
menu, you can turn on a logging feature, and use the same menu to open the
log as a CSV file. I usually turn the log on every time I start up EXCEL.
Last weekend, I was getting really slow update times -- reviewed the log
and found out each of my four OptionsXPress extractions took between 59 and
119 seconds. So they were exceptionally slow for some reason. It's also
handy to see how many web pages you're actually retrieving.
Apart from that general information, I'd need more specifics about what
you're retrieving.
However, since you're specifically asking about Yahoo, which is typically
very quick, I suspect it's either having a lot of individual
RCHGetYahooQuotes(
for a large number of ticker symbols (e.g. something from the "Key
Statistics" page).
On Mon, Oct 10, 2016 at 6:30 PM, neil@
...wrote:
> Is there a way of Speeding up yahoo data retrieval? When I select update
> it takes about 3 or 4 minutes to update the spreadsheet. Is it a function
> of processor speed or memory? Or distance - I live in Australia. Or is it
> just what it is?
>
>
Mon Oct 10, 2016 8:22 pm (PDT) . Posted by:
beatnut8827
Hi Randy Thanks. It's a bit of a mixtyre but mainly quotes. The array option won't work for my implementation. So will just have to be patient!
Neil
Neil
Mon Oct 10, 2016 8:46 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You might be able to do the array-entered retrieval in one worksheet, and
then use lookup formulas into that worksheet with your other worksheets
that actually use (or present) the data in a different configuration..
It has helped some people in the past when Yahoo quotes got funky, such as
having 92 functions work great, while 8 return an error of some type. If
you use the array-entered, you's still have an 8% failure rate, but it
would be all or nothing. Otherwise, recalculating might just have a
different 8 not work correctly.
On Mon, Oct 10, 2016 at 8:22 PM, neil@theatreofimage.com.au [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thanks. It's a bit of a mixtyre but mainly quotes. The array option won't
> work for my implementation. So will just have to be patient!
>
>
then use lookup formulas into that worksheet with your other worksheets
that actually use (or present) the data in a different configuration..
It has helped some people in the past when Yahoo quotes got funky, such as
having 92 functions work great, while 8 return an error of some type. If
you use the array-entered, you's still have an 8% failure rate, but it
would be all or nothing. Otherwise, recalculating might just have a
different 8 not work correctly.
On Mon, Oct 10, 2016 at 8:22 PM, neil@theatreofimage.com.au [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thanks. It's a bit of a mixtyre but mainly quotes. The array option won't
> work for my implementation. So will just have to be patient!
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar