EXCEL Stock Market Functions Add-in

Messages In This Digest (14 Messages)

1a.: Retrieval Limits & Best Practice From: another_investor
1b.: Re: Retrieval Limits & Best Practice From: Randy Harmelink
1c.: Re: Retrieval Limits & Best Practice From: another_investor

2a.: Retrieving Sectors/Industry From: Chirag Vyas
2b.: Re: Retrieving Sectors/Industry From: another_investor
2c.: Re: Retrieving Sectors/Industry From: Randy Harmelink

3a.: Re: Extracting forexpro data From: Stefn Rai
3b.: Re: Extracting forexpro data From: Randy Harmelink
3c.: Re: Extracting forexpro data From: Stefn Rai
3d.: Re: Extracting forexpro data From: Randy Harmelink

4a.: 13862 and/or 13863 - Company Name From: truemavsfan
4b.: Re: 13862 and/or 13863 - Company Name From: Randy Harmelink
4c.: Re: 13862 and/or 13863 - Company Name From: truemavsfan
4d.: Re: 13862 and/or 13863 - Company Name From: truemavsfan

View All Topics | Create New Topic

Messages

1a.: Retrieval Limits & Best Practice

Posted by: "another_investor" steviejacobs@hotmail.com another_investor

Fri May 11, 2012 3:04 am (PDT)

Hi Randy,

Problem
=======
I used to be able to retrieve the analyst estimates (CY, FY1, and FY2 EPS) from MSN and Yahoo for the S&P500 in a single worksheet simply by having 3 columns in excel for each site (so 6 columns in total) and using RCHGetElementNumber.
Over the past few weeks, it appears as though the plug-in is now returning 'Error' for each ticker after approx. 300 stocks (although the values are distributed around the sheet).
Stated another way, if I break the S&P500 into 2 sets of 250, then there are no problems with retrieving the values. If I leave it as a single sheet of 500 stocks, 'Error' will be returned for approx. 25% of the cells.

Questions
=========
I have read in previous posts regarding limits (of I think 1000 pages) and your comments on how it would be easy for any one of the data sources to block the plug-in so I want to ensure that I am not making too many web page calls at one time and that I am retrieving data efficiently, sensibly and not abusing this great tool.
I am unclear on how the plug-in works in that:
- the current, FY1 and FY2 analyst estimates are on a single webpage in Yahoo finance. Therefore am if I have three columns using RCHGetElementNumber (i.e. RCHGetElementNumber("IBM",599), RCHGetElementNumber("IBM",559), RCHGetElementNumber("IBM",560), am pulling the page 3 times or pulling the page once and then simply extracting the data from the cached page?
- for the cells that return 'Error', is there a way to use 'smfForceRecalculation' or is that just compounding the problem and making more calls to the website

I have searched previous posts and the links section but struggled to answer the question.
I am hoping you can answer my question and/or share some thoughts in terms of best design practice. My initial thinking is to make as few calls to the webpages as possible and to retrieve all the values I need into a single "reference" worksheet. I can then use those values into my own calculation/analysis workbook as needed.

Version
=======
I encounter the problem with both versions 'RCH_Stock_Market_Functions-2.1.2012.04.07' and 'RCH_Stock_Market_Functions-2.1.2012.02.02'. In addition, I have applied the latest version of 'smf-elements-1'.

Many thanks for all your great work!

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
1b.: Re: Retrieval Limits & Best Practice

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

Fri May 11, 2012 3:55 am (PDT)

Observations/answers/ramblings:

- There is a limit of 1000 web pages "per session" (which can be reset
with smfForceRecalculation or by exiting and restarting EXCEL). However,
smfForceRecalculation won't be of help if you have over 1000 web page
retrievals in a single workbook, since it would restart retrieval of EVERY
formula in the workbook, not just those that had errored out -- so you
would expect to error out again, although probably on different formulas,
because EXCEL doesn't recalculate in the exact same order every time.

- If three data elements are retrieved from the same web page, it's only
a single web page retrieval. The add-in saves the web page in an array
before it extracts data. So when the later extractions occur, it skips the
web page retrieval and goes to the stored copy of the web page. it's
actually WHY there is a 1000-page limit -- that's the size I set for the
storage area of retrieved web pages.

- You can usually find out the source of a data element by using "P-URL"
as the ticker symbols with the element number.

- Theoretically, if you are retrieving a single MSN web page and a
single Yahoo web page for each of the S&P 500 stocks, I would think you'd
never pass the 1000-page limit. However, note that if you have done
previous retrievals in other workbooks, they will count against the
1000-page limit -- until the saved array of web pages is purges via
smfForceRecalculation or by exiting/restarting EXCEL.

- For this type of data retrieval, I would convert it to the
smfUpdateDownloadTable. That would give you complete control over the
updating, since it places values into the workbook instead of using
recalculating formulas in the workbook.

- In the end, though, I would suggest bypassing MSN and Yahoo, and just
getting the data directly from Zacks. There, you can use the screener,
selecting stocks that belong to the S&P 500, and then output the fields
you're interested in. Then export the results -- much faster than
retrieving the data a web page at a time.

- You could also get all of your data in one Internet access from
FinViz. See the item in the "Links" area for info on doing that. Or use the
screener and get the export URL -- it can be used directly with the
smfGetCSVFile() function.

On Fri, May 11, 2012 at 3:04 AM, another_investor
<steviejacobs@hotmail.com>wrote:

> Hi Randy,
>
> Problem
> =======
> I used to be able to retrieve the analyst estimates (CY, FY1, and FY2 EPS)
> from MSN and Yahoo for the S&P500 in a single worksheet simply by having 3
> columns in excel for each site (so 6 columns in total) and using
> RCHGetElementNumber.
> Over the past few weeks, it appears as though the plug-in is now returning
> 'Error' for each ticker after approx. 300 stocks (although the values are
> distributed around the sheet).
> Stated another way, if I break the S&P500 into 2 sets of 250, then there
> are no problems with retrieving the values. If I leave it as a single sheet
> of 500 stocks, 'Error' will be returned for approx. 25% of the cells.
>
> Questions
> =========
> I have read in previous posts regarding limits (of I think 1000 pages) and
> your comments on how it would be easy for any one of the data sources to
> block the plug-in so I want to ensure that I am not making too many web
> page calls at one time and that I am retrieving data efficiently, sensibly
> and not abusing this great tool.
> I am unclear on how the plug-in works in that:
> - the current, FY1 and FY2 analyst estimates are on a single webpage in
> Yahoo finance. Therefore am if I have three columns using
> RCHGetElementNumber (i.e. RCHGetElementNumber("IBM",599),
> RCHGetElementNumber("IBM",559), RCHGetElementNumber("IBM",560), am pulling
> the page 3 times or pulling the page once and then simply extracting the
> data from the cached page?
> - for the cells that return 'Error', is there a way to use
> 'smfForceRecalculation' or is that just compounding the problem and making
> more calls to the website
>
> I have searched previous posts and the links section but struggled to
> answer the question.
> I am hoping you can answer my question and/or share some thoughts in terms
> of best design practice. My initial thinking is to make as few calls to the
> webpages as possible and to retrieve all the values I need into a single
> "reference" worksheet. I can then use those values into my own
> calculation/analysis workbook as needed.
>
> Version
> =======
> I encounter the problem with both versions
> 'RCH_Stock_Market_Functions-2.1.2012.04.07' and
> 'RCH_Stock_Market_Functions-2.1.2012.02.02'. In addition, I have applied
> the latest version of 'smf-elements-1'.
>
> Many thanks for all your great work!
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
1c.: Re: Retrieval Limits & Best Practice

Posted by: "another_investor" steviejacobs@hotmail.com another_investor

Fri May 11, 2012 5:48 am (PDT)

Thanks Randy for the prompt response and clarification.

I think my issue is the "1000 web page per session". For example, taking the S&P 500.
- MSN holds the ttm, CY, FY1, and FY2 for EPS on a single webpage.
- Yahoo holds CY, FY1 and FY2 on a single webpage but holds EPS (ttm) on a different webpage.

If I retrieve the values for the S&P500 into a table first, then I will have attempted to retrieve 1500 (500x3) webpages and exceeded the 1000 page-per-session limit.
However, if I use an excel formula to take all values from MSN for each ticker and then only to pull the Yahoo pages where the value in MSN is `NA' or `Error', then the number of pages is given by the formula.

No. of webpages = 500 MSN Pages + (Yahoo ttm where MSN ttm is missing/error) + (Yahoo EPS where MSN CY,FY1, or FY2 is missing error).

A quick sample test using 500 stocks results in 581 webpage retrievals. I am of course then given precedence to MSN's data over Yahoo's… but what data source is the most accurate is a different discussion altogether. :)

I am not sure the smfUpdateDownloadTable approach would work for the larger data set (but maybe you have any suggestions). I downloaded the example you provided and could use it (by changing the ticker and element values)… but I don't know how to create one to resolve the issue above.

In summary, I think I need to analyse all my data element requirements and group them into webpages by source. Since I now know the entire webpage is pulled, I may be able to reduce my total webpages by changing the source to an already used page.
I looked at, and will investigate further, the FinViz but I am not sure it will really help as some of the elements I use are missing and therefore I would need to pull other webpages which may defeat the point.

Finally, the "P-URL" comment was very useful!

Many, many thanks and all the best!

Steve

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Observations/answers/ramblings:
>
> - There is a limit of 1000 web pages "per session" (which can be reset
> with smfForceRecalculation or by exiting and restarting EXCEL). However,
> smfForceRecalculation won't be of help if you have over 1000 web page
> retrievals in a single workbook, since it would restart retrieval of EVERY
> formula in the workbook, not just those that had errored out -- so you
> would expect to error out again, although probably on different formulas,
> because EXCEL doesn't recalculate in the exact same order every time.
>
> - If three data elements are retrieved from the same web page, it's only
> a single web page retrieval. The add-in saves the web page in an array
> before it extracts data. So when the later extractions occur, it skips the
> web page retrieval and goes to the stored copy of the web page. it's
> actually WHY there is a 1000-page limit -- that's the size I set for the
> storage area of retrieved web pages.
>
> - You can usually find out the source of a data element by using "P-URL"
> as the ticker symbols with the element number.
>
> - Theoretically, if you are retrieving a single MSN web page and a
> single Yahoo web page for each of the S&P 500 stocks, I would think you'd
> never pass the 1000-page limit. However, note that if you have done
> previous retrievals in other workbooks, they will count against the
> 1000-page limit -- until the saved array of web pages is purges via
> smfForceRecalculation or by exiting/restarting EXCEL.
>
> - For this type of data retrieval, I would convert it to the
> smfUpdateDownloadTable. That would give you complete control over the
> updating, since it places values into the workbook instead of using
> recalculating formulas in the workbook.
>
> - In the end, though, I would suggest bypassing MSN and Yahoo, and just
> getting the data directly from Zacks. There, you can use the screener,
> selecting stocks that belong to the S&P 500, and then output the fields
> you're interested in. Then export the results -- much faster than
> retrieving the data a web page at a time.
>
> - You could also get all of your data in one Internet access from
> FinViz. See the item in the "Links" area for info on doing that. Or use the
> screener and get the export URL -- it can be used directly with the
> smfGetCSVFile() function.
>
>
> On Fri, May 11, 2012 at 3:04 AM, another_investor
> <steviejacobs@...>wrote:
>
> > Hi Randy,
> >
> > Problem
> > =======
> > I used to be able to retrieve the analyst estimates (CY, FY1, and FY2 EPS)
> > from MSN and Yahoo for the S&P500 in a single worksheet simply by having 3
> > columns in excel for each site (so 6 columns in total) and using
> > RCHGetElementNumber.
> > Over the past few weeks, it appears as though the plug-in is now returning
> > 'Error' for each ticker after approx. 300 stocks (although the values are
> > distributed around the sheet).
> > Stated another way, if I break the S&P500 into 2 sets of 250, then there
> > are no problems with retrieving the values. If I leave it as a single sheet
> > of 500 stocks, 'Error' will be returned for approx. 25% of the cells.
> >
> > Questions
> > =========
> > I have read in previous posts regarding limits (of I think 1000 pages) and
> > your comments on how it would be easy for any one of the data sources to
> > block the plug-in so I want to ensure that I am not making too many web
> > page calls at one time and that I am retrieving data efficiently, sensibly
> > and not abusing this great tool.
> > I am unclear on how the plug-in works in that:
> > - the current, FY1 and FY2 analyst estimates are on a single webpage in
> > Yahoo finance. Therefore am if I have three columns using
> > RCHGetElementNumber (i.e. RCHGetElementNumber("IBM",599),
> > RCHGetElementNumber("IBM",559), RCHGetElementNumber("IBM",560), am pulling
> > the page 3 times or pulling the page once and then simply extracting the
> > data from the cached page?
> > - for the cells that return 'Error', is there a way to use
> > 'smfForceRecalculation' or is that just compounding the problem and making
> > more calls to the website
> >
> > I have searched previous posts and the links section but struggled to
> > answer the question.
> > I am hoping you can answer my question and/or share some thoughts in terms
> > of best design practice. My initial thinking is to make as few calls to the
> > webpages as possible and to retrieve all the values I need into a single
> > "reference" worksheet. I can then use those values into my own
> > calculation/analysis workbook as needed.
> >
> > Version
> > =======
> > I encounter the problem with both versions
> > 'RCH_Stock_Market_Functions-2.1.2012.04.07' and
> > 'RCH_Stock_Market_Functions-2.1.2012.02.02'. In addition, I have applied
> > the latest version of 'smf-elements-1'.
> >
> > Many thanks for all your great work!
> >
>

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

2a.: Retrieving Sectors/Industry

Posted by: "Chirag Vyas" vyas.chirag@yahoo.in vyas.chirag

Fri May 11, 2012 3:31 am (PDT)

I want to retrieve sectors and Industry for the given list of US tickers. Which formula/code should I use?

Thanks

Regards,
Chirag Vyas

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2b.: Re: Retrieving Sectors/Industry

Posted by: "another_investor" steviejacobs@hotmail.com another_investor

Fri May 11, 2012 3:40 am (PDT)

Hi Chirag,

Depends on which website you want to pull it from. For example, from Yahoo from ticker 'IBM' it would be:

Sector = RCHGetElementNumber("IBM",13865)
Industry= RCHGetElementNumber("IBM",13867)

Hope this helps!

--- In smf_addin@yahoogroups.com, Chirag Vyas <vyas.chirag@...> wrote:
>
> I want to retrieve sectors and Industry for the given list of US tickers. Which formula/code should I use?
>
> Thanks
>
> Regards,
> Chirag Vyas
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2c.: Re: Retrieving Sectors/Industry

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

Fri May 11, 2012 3:58 am (PDT)

The bigger question would be WHOSE definitions you want. They vary between
data services.

I would suggest getting it from FinViz, so you can get it all at once. See
the "Links" area of the group for an entry on grabbing such data from the
FinViz screener.

You could also export such data from the Zacks screener, which would be
faster than doing it ticker by ticker if you have a big list.

On Fri, May 11, 2012 at 2:03 AM, Chirag Vyas <vyas.chirag@yahoo.in> wrote:

>
> I want to retrieve sectors and Industry for the given list of US tickers.
> Which formula/code should I use?
>

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

3a.: Re: Extracting forexpro data

Posted by: "Stefn Rai" rainerglobal@ymail.com rainerglobal@ymail.com

Fri May 11, 2012 8:41 am (PDT)

would anyone have some simple code for this?
best regards

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Friday, May 4, 2012 3:16 PM
Subject: Re: [smf_addin] Extracting forexpro data

You'd need to write your VBA timing routine for that.

On Fri, May 4, 2012 at 12:21 PM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
>Randy, thanks for the help, that works! However, the live prices does not update unless I press the smfForceREcalculation button. Any idea as to how to get the prices to update live, or at least more frequently, maybe every 10 seconds or so.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3b.: Re: Extracting forexpro data

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

Fri May 11, 2012 8:59 am (PDT)

Do a search for "Vba onTime"...

On Fri, May 11, 2012 at 8:41 AM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
> would anyone have some simple code for this?
> best regards
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3c.: Re: Extracting forexpro data

Posted by: "Stefn Rai" rainerglobal@ymail.com rainerglobal@ymail.com

Fri May 11, 2012 12:32 pm (PDT)

Ok, I am lost. Will : "Application.OnTime Now + TimeValue("00:00:15"), "smfForceRecalculation" work? Where do I put the code? In one of the modules to RCH_Stock_Market_Functions? I would like to update specific cells with RCHGetTableCell(http://www.forexpros.com/indices/indices-futures",2,"spx-500-futures") every 15 seconds or so.
Regards,

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Friday, May 11, 2012 10:59 AM
Subject: Re: [smf_addin] Extracting forexpro data

Do a search for "Vba onTime"...

On Fri, May 11, 2012 at 8:41 AM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
>would anyone have some simple code for this?
>best regards
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3d.: Re: Extracting forexpro data

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

Fri May 11, 2012 12:56 pm (PDT)

You'll have to try it to find out. I've never used it myself.

The code would need to be in your workbook, and you'll have to set up the
add-in as a reference library for the VBA project.

smfForceRecalculation will update everything though, not specific cells.

On Fri, May 11, 2012 at 12:32 PM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
>
> Ok, I am lost. Will : "Application.*OnTime* Now + TimeValue("00:00:15"),
> "smfForceRecalculation" work? Where do I put the code? In one of the
> modules to RCH_Stock_Market_Functions? I would like to update specific
> cells with RCHGetTableCell(
> http://www.forexpros.com/indices/indices-futures",2,<http://www.forexpros.com/indices/indices-futures%22,2>"spx-500-futures")
> every 15 seconds or so.
>

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

4a.: 13862 and/or 13863 - Company Name

Posted by: "truemavsfan" brad.valentine@hotmail.com truemavsfan

Fri May 11, 2012 8:46 am (PDT)

I have changed the elements 1.txt file and the last price is working, but I cannot get the company name to work, 13862. It returns "Error". The same is true for 13863. Is there a solution to fix this that you know of? Thanks.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4b.: Re: 13862 and/or 13863 - Company Name

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

Fri May 11, 2012 9:01 am (PDT)

What do you get for:

=RCHGetElementNumber("Version")

On Fri, May 11, 2012 at 8:46 AM, truemavsfan <brad.valentine@hotmail.com>wrote:

> I have changed the elements 1.txt file and the last price is working, but
> I cannot get the company name to work, 13862. It returns "Error". The
> same is true for 13863. Is there a solution to fix this that you know of?
> Thanks.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4c.: Re: 13862 and/or 13863 - Company Name

Posted by: "truemavsfan" brad.valentine@hotmail.com truemavsfan

Fri May 11, 2012 10:20 am (PDT)

Stock Market Functions add-in, Version 2.1.2010.08.02

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> What do you get for:
>
> =RCHGetElementNumber("Version")
>
> On Fri, May 11, 2012 at 8:46 AM, truemavsfan <brad.valentine@...>wrote:
>
> > I have changed the elements 1.txt file and the last price is working, but
> > I cannot get the company name to work, 13862. It returns "Error". The
> > same is true for 13863. Is there a solution to fix this that you know of?
> > Thanks.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4d.: Re: 13862 and/or 13863 - Company Name

Posted by: "truemavsfan" brad.valentine@hotmail.com truemavsfan

Fri May 11, 2012 10:24 am (PDT)

Problem solved. I was using an older version. Thanks for the help.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> What do you get for:
>
> =RCHGetElementNumber("Version")
>
> On Fri, May 11, 2012 at 8:46 AM, truemavsfan <brad.valentine@...>wrote:
>
> > I have changed the elements 1.txt file and the last price is working, but
> > I cannot get the company name to work, 13862. It returns "Error". The
> > same is true for 13863. Is there a solution to fix this that you know of?
> > Thanks.
> >
>

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

Create New Topic | Visit Your Group on the Web

Messages | Files | Photos | Polls

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

Sabtu, 12 Mei 2012

Messages In This Digest (14 Messages)

Messages

Posted by: "another_investor" steviejacobs@hotmail.com another_investor

Fri May 11, 2012 3:04 am (PDT)

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

Fri May 11, 2012 3:55 am (PDT)

Posted by: "another_investor" steviejacobs@hotmail.com another_investor

Fri May 11, 2012 5:48 am (PDT)

Posted by: "Chirag Vyas" vyas.chirag@yahoo.in vyas.chirag

Fri May 11, 2012 3:31 am (PDT)

Posted by: "another_investor" steviejacobs@hotmail.com another_investor

Fri May 11, 2012 3:40 am (PDT)

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

Fri May 11, 2012 3:58 am (PDT)

Posted by: "Stefn Rai" rainerglobal@ymail.com rainerglobal@ymail.com

Fri May 11, 2012 8:41 am (PDT)

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

Fri May 11, 2012 8:59 am (PDT)

Posted by: "Stefn Rai" rainerglobal@ymail.com rainerglobal@ymail.com

Fri May 11, 2012 12:32 pm (PDT)

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

Fri May 11, 2012 12:56 pm (PDT)

Posted by: "truemavsfan" brad.valentine@hotmail.com truemavsfan

Fri May 11, 2012 8:46 am (PDT)

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

Fri May 11, 2012 9:01 am (PDT)

Posted by: "truemavsfan" brad.valentine@hotmail.com truemavsfan

Fri May 11, 2012 10:20 am (PDT)

Posted by: "truemavsfan" brad.valentine@hotmail.com truemavsfan

Fri May 11, 2012 10:24 am (PDT)

Tidak ada komentar:

Posting Komentar

Followers

Blog Archive

About Me