8 Messages
Digest #3623
Messages
Tue Feb 2, 2016 12:45 pm (PST) . Posted by:
DefelRadar
Randy,
I believe my issue is isolated to me but I wanted to make sure. I've been using SMF for 12 years now and have not seen an issue with data downloads/calculations being slow like this before.
Basically what happens is if I want to update any data in my sheet(s) I either have to go to the VBA code and run the forcerecalc routine or use your menu to recalculate the sheet.
What is very odd is many times on the first recalculation not all formulas will return values properly (values that I know have came in in the past i.e. Current EPS). So if I go to the VBA code and run the smfforcerecalculation it takes a very long time 5-10 mins but eventually all the data comes in correctly.
I've also tried the smf menu option to recalculate selection and that works to bring the data in for an individual cell (almost instantly)
I've updated my smf addin to the most recent, updated my elements files to the most recent, and trimmed my excel workbook down to the minimum on formulas. I've also browsed the forum and tried several of the tips to modify the internet options to refresh website data on every visit etc. None of that has improved the calculation speed.
I'm at a loss as to why things are so slow. I know its not my internet connection.
Thanks for your help!
Aaron
I believe my issue is isolated to me but I wanted to make sure. I've been using SMF for 12 years now and have not seen an issue with data downloads/calculati
Basically what happens is if I want to update any data in my sheet(s) I either have to go to the VBA code and run the forcerecalc routine or use your menu to recalculate the sheet.
What is very odd is many times on the first recalculation not all formulas will return values properly (values that I know have came in in the past i.e. Current EPS). So if I go to the VBA code and run the smfforcerecalculati
I've also tried the smf menu option to recalculate selection and that works to bring the data in for an individual cell (almost instantly)
I've updated my smf addin to the most recent, updated my elements files to the most recent, and trimmed my excel workbook down to the minimum on formulas. I've also browsed the forum and tried several of the tips to modify the internet options to refresh website data on every visit etc. None of that has improved the calculation speed.
I'm at a loss as to why things are so slow. I know its not my internet connection.
Thanks for your help!
Aaron
Tue Feb 2, 2016 12:48 pm (PST) . Posted by:
DefelRadar
I'm using Excel 2010 on Windows 7 and IE 11. FYI
Tue Feb 2, 2016 1:16 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
If you're getting a lot of data, it will take a lot of time. Usually the
problem is people have individual RCHGetYahooQuotes() functions, one per
cell, instead of array-entering it over a range and getting them all in one
Internet access.
Some sites are slower than others. For example, if you were getting option
quotes from OptionsXPress, they take a lot longer than getting option
quotes from Yahoo.
One thing you can do is check the logs to see where you are spending your
time. That is:
1. Use context menu to Enable Logging
2. Run the smfForceRecalculation macro
3. Use context menu to Open Log File
Then, you'll have a workbook that shows you how many different Internet
requests you had and how long each one took. For example, some recent web
page retrievals the add-in did for me:
Time Stamp Duration Called URL
2016-01-28 12:51:56 0.6875
http://www.zacks.com/stock/research/MMM/earnings-announcements
2016-01-28 23:08:53 2.2109
http://quotes.morningstar.com/stock/c-morningstar-take?t=MMM
2016-01-28 23:21:38 0.9766
http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=MMM
2016-01-31 16:45:09 1.5547
http://www.amazon.com/s?field-price=0-0&sort=review-rank&lo=digital-text&rh=n:158580011,p_20:English&page=1
2016-01-31 16:45:11 1.0352 http://www.amazon.com/gp/product/B018KODRNO
2016-01-31 16:45:12 1.0195 http://www.amazon.com/gp/product/B012236WJ0
2016-01-31 16:45:13 1.0547 http://www.amazon.com/gp/product/B013JK2956
2016-01-31 17:24:54 36.1953
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=3/3/2016;1&Symbol=SPY
2016-01-31 17:26:00 15.6523
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=3/4/2016;1&Symbol=SPY
2016-01-31 17:34:09 17.2656
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=All&lstMarket=0&ChainType=14&lstMonths=02/19/2016;7&Symbol=SPY
As you can see, my last uses of OptionsXPress took a VERY long time to get
a response. One web page retrieval took 36 seconds! The reason those took
so long is they are for SPY, which displays a LOT of contracts. An average
company option quotes page might only take a second or two.
On Tue, Feb 2, 2016 at 1:45 PM, defelradar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Randy,
>
> I believe my issue is isolated to me but I wanted to make sure. I've been
> using SMF for 12 years now and have not seen an issue with data
> downloads/calculations being slow like this before.
>
> Basically what happens is if I want to update any data in my sheet(s) I
> either have to go to the VBA code and run the forcerecalc routine or use
> your menu to recalculate the sheet.
>
>
> What is very odd is many times on the first recalculation not all formulas
> will return values properly (values that I know have came in in the past
> i.e. Current EPS). So if I go to the VBA code and run the
> smfforcerecalculation it takes a very long time 5-10 mins but eventually
> all the data comes in correctly.
>
>
> I've also tried the smf menu option to recalculate selection and that
> works to bring the data in for an individual cell (almost instantly)
>
>
> I've updated my smf addin to the most recent, updated my elements files to
> the most recent, and trimmed my excel workbook down to the minimum on
> formulas. I've also browsed the forum and tried several of the tips to
> modify the internet options to refresh website data on every visit etc.
> None of that has improved the calculation speed.
>
>
> I'm at a loss as to why things are so slow. I know its not my internet
> connection.
>
>
> Thanks for your help!
>
problem is people have individual RCHGetYahooQuotes() functions, one per
cell, instead of array-entering it over a range and getting them all in one
Internet access.
Some sites are slower than others. For example, if you were getting option
quotes from OptionsXPress, they take a lot longer than getting option
quotes from Yahoo.
One thing you can do is check the logs to see where you are spending your
time. That is:
1. Use context menu to Enable Logging
2. Run the smfForceRecalculation macro
3. Use context menu to Open Log File
Then, you'll have a workbook that shows you how many different Internet
requests you had and how long each one took. For example, some recent web
page retrievals the add-in did for me:
Time Stamp Duration Called URL
2016-01-28 12:51:56 0.6875
http://www.zacks.com/stock/research/MMM/earnings-announcements
2016-01-28 23:08:53 2.2109
http://quotes.morningstar.com/stock/c-morningstar-take?t=MMM
2016-01-28 23:21:38 0.9766
http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=MMM
2016-01-31 16:45:09 1.5547
http://www.amazon.com/s?field-price=0-0&sort=review-rank&lo=digital-text&rh=n:158580011,p_20:English&page=1
2016-01-31 16:45:11 1.0352 http://www.amazon.com/gp/product/B018KODRNO
2016-01-31 16:45:12 1.0195 http://www.amazon.com/gp/product/B012236WJ0
2016-01-31 16:45:13 1.0547 http://www.amazon.com/gp/product/B013JK2956
2016-01-31 17:24:54 36.1953
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=3/3/2016;1&Symbol=SPY
2016-01-31 17:26:00 15.6523
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=3/4/2016;1&Symbol=SPY
2016-01-31 17:34:09 17.2656
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=All&lstMarket=0&ChainType=14&lstMonths=02/19/2016;7&Symbol=SPY
As you can see, my last uses of OptionsXPress took a VERY long time to get
a response. One web page retrieval took 36 seconds! The reason those took
so long is they are for SPY, which displays a LOT of contracts. An average
company option quotes page might only take a second or two.
On Tue, Feb 2, 2016 at 1:45 PM, defelradar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Randy,
>
> I believe my issue is isolated to me but I wanted to make sure. I've been
> using SMF for 12 years now and have not seen an issue with data
> downloads/calculati
>
> Basically what happens is if I want to update any data in my sheet(s) I
> either have to go to the VBA code and run the forcerecalc routine or use
> your menu to recalculate the sheet.
>
>
> What is very odd is many times on the first recalculation not all formulas
> will return values properly (values that I know have came in in the past
> i.e. Current EPS). So if I go to the VBA code and run the
> smfforcerecalculati
> all the data comes in correctly.
>
>
> I've also tried the smf menu option to recalculate selection and that
> works to bring the data in for an individual cell (almost instantly)
>
>
> I've updated my smf addin to the most recent, updated my elements files to
> the most recent, and trimmed my excel workbook down to the minimum on
> formulas. I've also browsed the forum and tried several of the tips to
> modify the internet options to refresh website data on every visit etc.
> None of that has improved the calculation speed.
>
>
> I'm at a loss as to why things are so slow. I know its not my internet
> connection.
>
>
> Thanks for your help!
>
Tue Feb 2, 2016 1:18 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That option is pretty much useless for most add-in functions. It just does
an EXCEL recalculate. For something like an RCHGetElementNumber() function,
it doesn't go and get a fresh copy of the web page from the Internet. The
smfForceRecalculation process can only be done as an "all or nothing" type
thing.
On Tue, Feb 2, 2016 at 1:45 PM, defelradar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I've also tried the smf menu option to recalculate selection and that
> works to bring the data in for an individual cell (almost instantly)
>
>
an EXCEL recalculate. For something like an RCHGetElementNumber() function,
it doesn't go and get a fresh copy of the web page from the Internet. The
smfForceRecalculation process can only be done as an "all or nothing" type
thing.
On Tue, Feb 2, 2016 at 1:45 PM, defelradar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I've also tried the smf menu option to recalculate selection and that
> works to bring the data in for an individual cell (almost instantly)
>
>
Tue Feb 2, 2016 4:05 pm (PST) . Posted by:
gotsaurabh
I have a spreadsheet that uses RCHGetelementNumber() to get FY1 and FY2 EPS estimates for stocks. I would like to also get the "FY0" EPS metric. This is shown on the Yahoo Finance Analyst Estimates page as "Year Ago EPS" under the Current Year estimate. I can't figure out a way to get it automatically in a spreadsheet. The "FY0" metric is useful for those stocks which don't have a fiscal year ending on Dec 31, in order to convert fiscal year estimates into current calendar year estimates.
Tue Feb 2, 2016 6:47 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Just use the element:
# Usage Source Element Template or Notes
595 S YahooAE Earnings Estimates -- Average Estimate -- FY1
SMF-Template-Yahoo-Analyst-Estimates.xls
596 S YahooAE Earnings Estimates -- Average Estimate -- FY2
SMF-Template-Yahoo-Analyst-Estimates.xls
599 S YahooAE Earnings Estimates -- Year Ago EPS -- FY1
SMF-Template-Yahoo-Analyst-Estimates.xls
600 S YahooAE Earnings Estimates -- Year Ago EPS -- FY2
SMF-Template-Yahoo-Analyst-Estimates.xls
From your description, I assume you're using elements #595 and #596 for FY1
and FY2 estimates? The FY0 metric, or "Year Ago EPS" for FY1 would just be
element #599?
Note that element #600 SHOULD have the same value as element #595, since
that estimate is all they would have available for the "Year Ago EPS" for
FY2 because there would be no actual earnings amounts.
Or do I not understand the question?
On Tue, Feb 2, 2016 at 5:05 PM, gotsaurabh@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I have a spreadsheet that uses RCHGetelementNumber() to get FY1 and FY2
> EPS estimates for stocks. I would like to also get the "FY0" EPS metric.
> This is shown on the Yahoo Finance Analyst Estimates page as "Year Ago EPS"
> under the Current Year estimate. I can't figure out a way to get it
> automatically in a spreadsheet. The "FY0" metric is useful for those stocks
> which don't have a fiscal year ending on Dec 31, in order to convert fiscal
> year estimates into current calendar year estimates.
>
>
# Usage Source Element Template or Notes
595 S YahooAE Earnings Estimates -- Average Estimate -- FY1
SMF-Template-Yahoo-Analyst-Estimates.xls
596 S YahooAE Earnings Estimates -- Average Estimate -- FY2
SMF-Template-Yahoo-Analyst-Estimates.xls
599 S YahooAE Earnings Estimates -- Year Ago EPS -- FY1
SMF-Template-Yahoo-Analyst-Estimates.xls
600 S YahooAE Earnings Estimates -- Year Ago EPS -- FY2
SMF-Template-Yahoo-Analyst-Estimates.xls
From your description, I assume you're using elements #595 and #596 for FY1
and FY2 estimates? The FY0 metric, or "Year Ago EPS" for FY1 would just be
element #599?
Note that element #600 SHOULD have the same value as element #595, since
that estimate is all they would have available for the "Year Ago EPS" for
FY2 because there would be no actual earnings amounts.
Or do I not understand the question?
On Tue, Feb 2, 2016 at 5:05 PM, gotsaurabh@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I have a spreadsheet that uses RCHGetelementNumber
> EPS estimates for stocks. I would like to also get the "FY0" EPS metric.
> This is shown on the Yahoo Finance Analyst Estimates page as "Year Ago EPS"
> under the Current Year estimate. I can't figure out a way to get it
> automatically in a spreadsheet. The "FY0" metric is useful for those stocks
> which don't have a fiscal year ending on Dec 31, in order to convert fiscal
> year estimates into current calendar year estimates.
>
>
Tue Feb 2, 2016 11:04 pm (PST) . Posted by:
aarj14
I was searching for financial data and was referenced here as a place where excel is used to collect data. I legitimately am pretty lost and not sure where to find the FAQ or instructions. All help is appreciated, thank you!
Tue Feb 2, 2016 11:42 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
For the Add-in, Documentation, Templates, Tips and FAQs, visit
http://ogres-crypt.com/SMF
There are several functions that are used most often:
------------------------------
*RCHGetYahooQuotes()*
This function returns delayed stock quotes and other data from the Yahoo
quotes interface. For example, this formula, array-entered over a 2-row by
3-column range, would return the last trading price, the price/sales ratio,
and the price/book ratio for "IBM" and "MMM":
=RCHGetYahooQuotes("IBM,MMM", "l1p5p6")
------------------------------
*RCHGetYahooHistory()*
This function returns historical stock quotes from the Yahoo historical
quotes interface. For example, this array-entered formula would return a
range of data containing historical quotes data from 6/1/2006 thru
6/16/2006 for "IBM":
=RCHGetYahooHistory("IBM", 2006, 6, 1, 2006, 6, 16)
------------------------------
*RCHGetElementNumber()*
This function allows you to easily access thousands of "saved" add-in
functions. For example,this formula would return the Market Capitalization
amount from Yahoo's Key Statistics page for ticker "IBM":
=RCHGetElementNumber("IBM", 941)
The definitions of the elements are in the smf-elements-*.txt files that
are distributed with the add-in. They need to be in the folder where the
add-in is stored. The XLS file distributed with the add-in is documentation
of all of the elements available. Current versions of these files is
available on the web site here:
http://ogres-crypt.com/SMF/Elements/
------------------------------
*RCHGetTableCell()*
This function extracts a specified table cell from a web page. For
example,this formula would return the Market Capitalization amount from
Yahoo's Key Statistics page for ticker "IBM" (just as the
RCHGetElementNumber() example above did):
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
------------------------------
*smfGetTagContents()*
This function extracts data from within a specified HTML tag on the web
page. For example,this formula would return the Market Capitalization
amount from Yahoo's Key Statistics page for ticker "IBM" (just as the
RCHGetElementNumber() and RCHGetTableCell() examples above did):
=smfGetTagContent("http://finance.yahoo.com/q/ks?s=IBM","td",1,">Market
Cap",,,,1)
------------------------------
On Tue, Feb 2, 2016 at 11:07 PM, aarj14@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I was searching for financial data and was referenced here as a place
> where excel is used to collect data. I legitimately am pretty lost and not
> sure where to find the FAQ or instructions. All help is appreciated, thank
> you!
>
>
http://ogres-crypt.com/SMF
There are several functions that are used most often:
------------------------------
*RCHGetYahooQuotes()*
This function returns delayed stock quotes and other data from the Yahoo
quotes interface. For example, this formula, array-entered over a 2-row by
3-column range, would return the last trading price, the price/sales ratio,
and the price/book ratio for "IBM" and "MMM":
=RCHGetYahooQuotes("IBM,MMM", "l1p5p6")
------------------------------
*RCHGetYahooHistory()*
This function returns historical stock quotes from the Yahoo historical
quotes interface. For example, this array-entered formula would return a
range of data containing historical quotes data from 6/1/2006 thru
6/16/2006 for "IBM":
=RCHGetYahooHistory("IBM", 2006, 6, 1, 2006, 6, 16)
------------------------------
*RCHGetElementNumber()*
This function allows you to easily access thousands of "saved" add-in
functions. For example,this formula would return the Market Capitalization
amount from Yahoo's Key Statistics page for ticker "IBM":
=RCHGetElementNumber("IBM", 941)
The definitions of the elements are in the smf-elements-*.txt files that
are distributed with the add-in. They need to be in the folder where the
add-in is stored. The XLS file distributed with the add-in is documentation
of all of the elements available. Current versions of these files is
available on the web site here:
http://ogres-crypt.com/SMF/Elements/
------------------------------
*RCHGetTableCell()*
This function extracts a specified table cell from a web page. For
example,this formula would return the Market Capitalization amount from
Yahoo's Key Statistics page for ticker "IBM" (just as the
RCHGetElementNumber() example above did):
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
------------------------------
*smfGetTagContents()*
This function extracts data from within a specified HTML tag on the web
page. For example,this formula would return the Market Capitalization
amount from Yahoo's Key Statistics page for ticker "IBM" (just as the
RCHGetElementNumber() and RCHGetTableCell() examples above did):
=smfGetTagContent("http://finance.yahoo.com/q/ks?s=IBM","td",1,">Market
Cap",,,,1)
------------------------------
On Tue, Feb 2, 2016 at 11:07 PM, aarj14@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I was searching for financial data and was referenced here as a place
> where excel is used to collect data. I legitimately am pretty lost and not
> sure where to find the FAQ or instructions. All help is appreciated, thank
> you!
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar