Jumat, 27 Januari 2012

[smf_addin] Digest Number 2090

Messages In This Digest (12 Messages)

Messages

1a.

Excel Limits

Posted by: "Russell M" ru88ell@gmail.com   rerussellson

Thu Jan 26, 2012 8:17 am (PST)



Hello -

I am new to your group and finding this tool extremely helpful towards my goal of finding a Bloomberg replacement for creating analytical spreadsheets.

My latest project is creating a worksheet that analyzes the S&P Total Market Index for a bunch of different indicators (14; mostly Income Statement, Balance Sheet, Cash Flow, stuff).

The sheet is set up with rows of stocks and columns of indicators. I do not use arrays as each cell has an element such as "=RCHGetElementNumber(B2,966)/1000".

The problem I am having is that after awhile a number of cells get an "Error" where there should be information. Seems like the sheet only gets data for 250 or so rows and then errors out the rest.

Is there a limit to the capacity of the sheet?

I tried searching prior posts but could not find any helpful information.

Any help would be greatly appreciated.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
1b.

Re: Excel Limits

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jan 26, 2012 8:27 am (PST)



The add-in is only set up to access 1000 web pages at a time. More details
on how web pages are retrieved and have data extracted from them can be
found in the "Links" area, under the smfForceRecalculation macro link.

That should be more than enough for reasonable data retrieval. I don't want
people building databases of information using the free data sources. It's
unfair to burden them in that way. It would be easy enough for them tor
change their web pages so the add-in couldn't extract any data.

You should be using one of the online screeners to reduce the number of
tickers first, and then getting the more detailed information on a smaller
set of ticker symbols.

On Thu, Jan 26, 2012 at 9:03 AM, Russell M <ru88ell@gmail.com> wrote:

>
> I am new to your group and finding this tool extremely helpful towards my
> goal of finding a Bloomberg replacement for creating analytical
> spreadsheets.
>
> My latest project is creating a worksheet that analyzes the S&P Total
> Market Index for a bunch of different indicators (14; mostly Income
> Statement, Balance Sheet, Cash Flow, stuff).
>
> The sheet is set up with rows of stocks and columns of indicators. I do
> not use arrays as each cell has an element such as
> "=RCHGetElementNumber(B2,966)/1000".
>
> The problem I am having is that after awhile a number of cells get an
> "Error" where there should be information. Seems like the sheet only gets
> data for 250 or so rows and then errors out the rest.
>
> Is there a limit to the capacity of the sheet?
>
> I tried searching prior posts but could not find any helpful information.
>
> Any help would be greatly appreciated.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
1c.

Re: Excel Limits

Posted by: "Russell M" ru88ell@gmail.com   rerussellson

Thu Jan 26, 2012 11:30 am (PST)



Thank you very much for the response. Your recommendation makes perfect sense.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The add-in is only set up to access 1000 web pages at a time. More details
> on how web pages are retrieved and have data extracted from them can be
> found in the "Links" area, under the smfForceRecalculation macro link.
>
> That should be more than enough for reasonable data retrieval. I don't want
> people building databases of information using the free data sources. It's
> unfair to burden them in that way. It would be easy enough for them tor
> change their web pages so the add-in couldn't extract any data.
>
> You should be using one of the online screeners to reduce the number of
> tickers first, and then getting the more detailed information on a smaller
> set of ticker symbols.
>
> On Thu, Jan 26, 2012 at 9:03 AM, Russell M <ru88ell@...> wrote:
>
> >
> > I am new to your group and finding this tool extremely helpful towards my
> > goal of finding a Bloomberg replacement for creating analytical
> > spreadsheets.
> >
> > My latest project is creating a worksheet that analyzes the S&P Total
> > Market Index for a bunch of different indicators (14; mostly Income
> > Statement, Balance Sheet, Cash Flow, stuff).
> >
> > The sheet is set up with rows of stocks and columns of indicators. I do
> > not use arrays as each cell has an element such as
> > "=RCHGetElementNumber(B2,966)/1000".
> >
> > The problem I am having is that after awhile a number of cells get an
> > "Error" where there should be information. Seems like the sheet only gets
> > data for 250 or so rows and then errors out the rest.
> >
> > Is there a limit to the capacity of the sheet?
> >
> > I tried searching prior posts but could not find any helpful information.
> >
> > Any help would be greatly appreciated.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2a.

smfgetoptionquotes

Posted by: "jperugi" jperugi@yahoo.com   jperugi

Thu Jan 26, 2012 7:07 pm (PST)



When I try to acquire the nearest strike price for a particular stock, I receive the text "Error". I used the following formula: =smfGetOptionQuotes(B4&" Mar 2012 ITM1 Call","s",0,"MW") where B4 is the stock quote reference cell.

Any idea what I am doing wrong?

Thanks so much for your help.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2b.

Re: smfgetoptionquotes

Posted by: "Randy H" rharmelink@gmail.com   rharmelink

Thu Jan 26, 2012 7:23 pm (PST)



Marketwatch changed their option symbols late last year, and I haven't
found them to be a reliable source since, so I haven't made any changes
to my routines. I'm not sure I even understand the coding of their
current option symbols. And, for example, if I look at the March options
for SPY, there is only one line for each strike price -- but some are
the monthly option and some are the quarterly option. There should be
one strike price line for each. They used to list the monthly and
quarterly options in separate tables.

The current beta version of the add-in in the "Works in Progress" folder
has a function to get ITM/OTM strike prices from Google, Yahoo, or
OptionsXPress. It can even generate the necessary option symbol for
smfGetOptionQuotes().

On Thu, Jan 26, 2012 at 7:59 PM, jperugi <jperugi@yahoo.com> wrote:
When I try to acquire the nearest strike price for a particular stock, I
receive the text "Error". I used the following formula:
=smfGetOptionQuotes(B4&" Mar 2012 ITM1 Call","s",0,"MW") where B4 is the
stock quote reference cell.

Any idea what I am doing wrong?

Thanks so much for your help.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3a.

Error -- Too many web page retrievals

Posted by: "tanya_1bg" tanya_1bg@yahoo.com   tanya_1bg

Thu Jan 26, 2012 8:51 pm (PST)



Hi -
I tried pulling the Earnings Growth Rate (5-Year Annual Average) using =RCHGetElementNumber(Ticker,66) which appears to source from MSN. Received an error message: Error -- Too many web page retrievals

Is there a way around this? Alternatively, is there another "pullable" element that will give me the past 5-yr Earnings growth rate? Can also use EPS growth rate (past 5-yr avg).

Would greatly appreciate anybody's help on this!

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
3b.

Re: Error -- Too many web page retrievals

Posted by: "Randy H" rharmelink@gmail.com   rharmelink

Thu Jan 26, 2012 8:58 pm (PST)



The add-in isn't intended to burden the free data sources with so many
data requests. The add-in can only access 1000 web pages at a time. Why
do you need so much data? How many companies were you attempting to
retrieve data on?

You should be using some type of online screener to narrow down the
number of stocks you need to retrieve data on, and then retrieve
detailed data on that smaller pool of stocks.

Besides, if you use the FinViz or Zacks screeners, you can export that
element for all ticker symbols in a single download file, instead of
retrieving them one by one for each ticker symbol.

On Thu, Jan 26, 2012 at 9:48 PM, tanya_1bg <tanya_1bg@yahoo.com> wrote:
Hi -
I tried pulling the Earnings Growth Rate (5-Year Annual Average) using
=RCHGetElementNumber(Ticker,66) which appears to source from MSN.
Received an error message: Error -- Too many web page retrievals

Is there a way around this? Alternatively, is there another "pullable"
element that will give me the past 5-yr Earnings growth rate? Can also
use EPS growth rate (past 5-yr avg).

Would greatly appreciate anybody's help on this!

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
3c.

Re: Error -- Too many web page retrievals

Posted by: "Tatyana Donova" tanya_1bg@yahoo.com   tanya_1bg

Thu Jan 26, 2012 9:23 pm (PST)



Hi,

I am trying to retrieve a total of 500 data points - 1 data point for each of the 500 companies comprising the S&P 500 index. This looks doable considering the data limitations. Am I using the incorrect element for 5-year average growth rates?

Many thanks again.

________________________________
From: Randy H <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, January 26, 2012 11:58 PM
Subject: [smf_addin] Re: Error -- Too many web page retrievals


 
The add-in isn't intended to burden the free data sources with so many data requests. The add-in can only access 1000 web pages at a time. Why do you need so much data? How many companies were you attempting to retrieve data on?

You should be using some type of online screener to narrow down the number of stocks you need to retrieve data on, and then retrieve detailed data on that smaller pool of stocks.

Besides, if you use the FinViz or Zacks screeners, you can export that element for all ticker symbols in a single download file, instead of retrieving them one by one for each ticker symbol.

On Thu, Jan 26, 2012 at 9:48 PM, tanya_1bg <tanya_1bg@yahoo.com> wrote:

Hi -
>I tried pulling the Earnings Growth Rate (5-Year Annual Average) using =RCHGetElementNumber(Ticker, 66) which appears to source from MSN. Received an error message: Error -- Too many web page retrievals
>
>Is there a way around this? Alternatively, is there another "pullable" element that will give me the past 5-yr Earnings growth rate? Can also use EPS growth rate (past 5-yr avg).
>
>Would greatly appreciate anybody's help on this!
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
3d.

Re: Error -- Too many web page retrievals

Posted by: "Randy H" rharmelink@gmail.com   rharmelink

Thu Jan 26, 2012 9:38 pm (PST)



That should only be 500 web pages. Did you retrieve a lot of other data
before attempting this one?

In any case, this would get you the whole list in one Internet access:

=smfGetCSVFIle("http://finviz.com/export.ashx?v=152&f=idx_sp500&c=1,19")

That is this screen:

http://finviz.com/screener.ashx?v=152&f=idx_sp500&c=1,19

Also, note that the stated growth rate varies significantly between the
various data sources.

On Thu, Jan 26, 2012 at 10:23 PM, Tatyana Donova <tanya_1bg@yahoo.com>
wrote:

I am trying to retrieve a total of 500 data points - 1 data point for
each of the 500 companies comprising the S&P 500 index. This looks
doable considering the data limitations. Am I using the incorrect
element for 5-year average growth rates?
Many thanks again.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
3e.

Re: Error -- Too many web page retrievals

Posted by: "Tatyana Donova" tanya_1bg@yahoo.com   tanya_1bg

Thu Jan 26, 2012 11:24 pm (PST)



Thank you Randy!

________________________________
From: Randy H <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Friday, January 27, 2012 12:38 AM
Subject: [smf_addin] Re: Error -- Too many web page retrievals


 
That should only be 500 web pages. Did you retrieve a lot of other data before attempting this one?

In any case, this would get you the whole list in one Internet access:

=smfGetCSVFIle("http://finviz.com/export.ashx?v=152&f=idx_sp500&c=1,19")

That is this screen:

http://finviz.com/screener.ashx?v=152&f=idx_sp500&c=1,19

Also, note that the stated growth rate varies significantly between the various data sources.

On Thu, Jan 26, 2012 at 10:23 PM, Tatyana Donova <tanya_1bg@yahoo.com> wrote:

>
>I am trying to retrieve a total of 500 data points - 1 data
point for each of the 500 companies comprising the S&P 500 index.
This looks doable considering the data limitations. Am I using the
incorrect element for 5-year average growth rates?
>
>
>Many thanks again.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
4a.

=RCHGetYahooHistory updating very slow

Posted by: "draz2121" bolony21@gmail.com   draz2121

Fri Jan 27, 2012 2:23 am (PST)



Hi,

I am using =RCHGetYahooHistory(B1,,,,,,,,,,1,1)

.....where B1 is the ticker to fetch the following EOD data from Yahoo:

Date Adj. Open Adj. High Adj. Low Adj. Close

The problem is, the LSE opens at 8am GMT and none of my 64 ticker data is updated before this time.

After 8am, they start to update in dribs and drabs.

At the time of writing (10:22 GMT)there are still 5 that have not updated to show yesterday's EOD data.

Is there anything I can do to get this data sooner and I need it before market open to make the most use of it.

Thanks.

Shaun

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4b.

Re: =RCHGetYahooHistory updating very slow

Posted by: "Randy H" rharmelink@gmail.com   rharmelink

Fri Jan 27, 2012 2:52 am (PST)



Where isn't it updated? On Yahoo, or in your workbook? If it's not
updated on Yahoo, you'll need to find a different source that has
updated data available in time for your needs. If the workbook, I'm
baffled -- it usually takes less than a second for me to get historical
quotes on a stock.

On Fri, Jan 27, 2012 at 3:23 AM, draz2121 <bolony21@gmail.com> wrote:

I am using =RCHGetYahooHistory(B1,,,,,,,,,,1,1)

.....where B1 is the ticker to fetch the following EOD data from Yahoo:

Date Adj. Open Adj. High Adj. Low Adj. Close

The problem is, the LSE opens at 8am GMT and none of my 64 ticker data
is updated before this time.

After 8am, they start to update in dribs and drabs.

At the time of writing (10:22 GMT)there are still 5 that have not
updated to show yesterday's EOD data.

Is there anything I can do to get this data sooner and I need it before
market open to make the most use of it.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Fashion News

What's the word on

fashion and style?

Search Ads

Get new customers.

List your web site

in Yahoo! Search.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
Diposting oleh EXCEL FEED LEARNING di 02.57

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)