[smf_addin] Digest Number 2253

EXCEL Stock Market Functions Add-in

Messages In This Digest (9 Messages)

1a.

Re: RCHGetYahooQuotes Array with Autofilter From: back

1b.

Re: RCHGetYahooQuotes Array with Autofilter From: Randy Harmelink

2a.

Re: Quick Start From: sophoclessophocleous

2b.

Re: Quick Start From: sophoclessophocleous

2c.

Re: Quick Start From: Randy Harmelink

3a.

Re: TheStreet ETF Screener From: carmine288

3b.

Re: TheStreet ETF Screener From: Randy Harmelink

4a.

Re: GetWebData can't update data timely From: bobc94595

4b.

Re: GetWebData can't update data timely From: Randy Harmelink

View All Topics | Create New Topic

Messages

1a.

Re: RCHGetYahooQuotes Array with Autofilter

Posted by: "back" backmehl@yahoo.de   backmehl

Wed Jun 20, 2012 2:32 am (PDT)

Hi,

actual quotes means the Last Price for the filtered symbols ...

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I have no idea what "actual quotes" means. Besides, if you have 20000
> symbols, you're going to need to have at least 100 invocations of the
> function.
>
> On Tue, Jun 19, 2012 at 11:49 PM, back <backmehl@...> wrote:
>
> >
> > yes ok, but in my list there are nearer to 20000 symbols, so i only need
> > the actual quotes if the filter is on ...
> >
> > --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> > >
> > > I do the formula first, then apply the filter to the results...
> > >
> > > On Tue, Jun 19, 2012 at 6:54 AM, back <backmehl@> wrote:
> > > >
> > > > if i start the Autofilter and insert the Formula - only visible cells
> > -, i
> > > > get an error message ...
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

1b.

Re: RCHGetYahooQuotes Array with Autofilter

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

Wed Jun 20, 2012 2:51 am (PDT)

It just makes no sense to me what you're trying to do...

On Wed, Jun 20, 2012 at 2:32 AM, back <backmehl@yahoo.de> wrote:

>
> actual quotes means the Last Price for the filtered symbols ...
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)

2a.

Re: Quick Start

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

Wed Jun 20, 2012 6:22 am (PDT)

I'm starting to understand how this works. I understand the main problem is out of your control as the website changes effect the output and hence my confussion.

I would like to use the AdvFN data but it doesn't seem to work?

I tested the google data with the following formula:
=RCHGetElementNumber("GOOG",3308) and it works. Please note that Google no longer works for 6yrs. It only goes back 4 yrs so 3312 and 3313 give an error.

Now I tried AdvFN with the following formula but nothing:
=RCHGetElementNumber("GOOG",5296)

I thought perhaps I needed to log on so I registered and tried again. Nothing.

For AdvFn, do I need to use smfGetAdvFNElement or something else instead ?

What is the correct formula?

Thank you,
Sophocles

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The issue with MorningStar is that they have converted several pages to
> dynamic loading of the data onto the page. However, the add-in extracts
> data from the source code of the web page. So there is no data to extract
> from the source code of the web page if the data is presented dynamically.
>
> I considered the installation documentation to be the quick start guide. It
> summarizes several of the more common functions and points to their
> documentation, each of which have examples. The available elements are
> documented in the XLS file that was in the ZIP archive.
>
> Most people tend to go to AdvFN for data, simply because it has a wider
> breadth of data available -- both in number of time periods and line items
> for each time period.
>
> On Tue, Jun 19, 2012 at 5:11 AM, sophoclessophocleous <
> sophoclessophocleous@...> wrote:
>
> >
> > I discovered this yty and I would like to both thank you and congratulate
> > you. What you have done is absolutely amazing!
> >
> > My only question is regarding sources. I have been looking through some
> > of the spreadsheets and one I found from a different site and it appears
> > that some sources have changed. For example, I understand that morningstar
> > income statement figures return an error.
> >
> > What are the current most reliable sources? And is there a list of
> > elements to reference?
> >
> > Also, I would appreciate it if you could please give me an example of a
> > code that works in excel for the income statement. For example, Sales.
> > I need something in place of
> >
> > RCHGetTableCell("
> > http://quicktake.morningstar.com/Stock/Income10.asp?Symbol="&Ticker, 2,
> > "Fiscal Year-End:", ">Revenue")
> >
> > I have gone through the various files but am still having some trouble.
> >
> > This project is amazing and very helpful. My only comment is that a quick
> > start guide is needed with a) current best sources, b)codes/elements, c) a
> > couple of examples.
> >
> > Thank you and all the best!!!
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2b.

Re: Quick Start

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

Wed Jun 20, 2012 6:27 am (PDT)

This is the 2nd time I writing this. Unfortunately my very lengthy first reply may have been lost :(
Overall I am starting to understand but am having problems with the data.
This formula works:
=RCHGetElementNumber("GOOG",3308)

Please note that 3312 and 3313 produce an error and I guess it is because google now only gives 4 yrs of data.

What is the equivalent for AdvFN?
I tried =RCHGetElementNumber("GOOG",5296)
But that does not work. I even registered for their website but still nothing.

Do I need to use some other formula?

Thanks,
Sophocles

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The issue with MorningStar is that they have converted several pages to
> dynamic loading of the data onto the page. However, the add-in extracts
> data from the source code of the web page. So there is no data to extract
> from the source code of the web page if the data is presented dynamically.
>
> I considered the installation documentation to be the quick start guide. It
> summarizes several of the more common functions and points to their
> documentation, each of which have examples. The available elements are
> documented in the XLS file that was in the ZIP archive.
>
> Most people tend to go to AdvFN for data, simply because it has a wider
> breadth of data available -- both in number of time periods and line items
> for each time period.
>
> On Tue, Jun 19, 2012 at 5:11 AM, sophoclessophocleous <
> sophoclessophocleous@...> wrote:
>
> >
> > I discovered this yty and I would like to both thank you and congratulate
> > you. What you have done is absolutely amazing!
> >
> > My only question is regarding sources. I have been looking through some
> > of the spreadsheets and one I found from a different site and it appears
> > that some sources have changed. For example, I understand that morningstar
> > income statement figures return an error.
> >
> > What are the current most reliable sources? And is there a list of
> > elements to reference?
> >
> > Also, I would appreciate it if you could please give me an example of a
> > code that works in excel for the income statement. For example, Sales.
> > I need something in place of
> >
> > RCHGetTableCell("
> > http://quicktake.morningstar.com/Stock/Income10.asp?Symbol="&Ticker, 2,
> > "Fiscal Year-End:", ">Revenue")
> >
> > I have gone through the various files but am still having some trouble.
> >
> > This project is amazing and very helpful. My only comment is that a quick
> > start guide is needed with a) current best sources, b)codes/elements, c) a
> > couple of examples.
> >
> > Thank you and all the best!!!
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2c.

Re: Quick Start

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

Wed Jun 20, 2012 8:36 am (PDT)

Take a look at the AdvFN item in the LINKS area of the group. Some people
have needed that fix before AdvFN would work.

Another issue might be if you are in a foreign country. The AdvFN website
does redirect people to different URLs based on their location, which can
affect how you access their data.

On Wed, Jun 20, 2012 at 6:27 AM, sophoclessophocleous <
sophoclessophocleous@yahoo.com> wrote:

> This is the 2nd time I writing this. Unfortunately my very lengthy first
> reply may have been lost :(
> Overall I am starting to understand but am having problems with the data.
> This formula works:
> =RCHGetElementNumber("GOOG",3308)
>
> Please note that 3312 and 3313 produce an error and I guess it is because
> google now only gives 4 yrs of data.
>
> What is the equivalent for AdvFN?
> I tried =RCHGetElementNumber("GOOG",5296)
> But that does not work. I even registered for their website but still
> nothing.
>
> Do I need to use some other formula?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3a.

Re: TheStreet ETF Screener

Posted by: "carmine288" carmine.nicoletta@gmail.com   carmine288

Wed Jun 20, 2012 9:22 am (PDT)

Why these formulas don't work for ticker ACN?

--- In smf_addin@yahoogroups.com, "Randy Harmelink" <rharmelink@...> wrote:
>
> I started from something that was posted in the past:
>
> http://finance.groups.yahoo.com/group/smf_addin/message/3362
>
> It appears you need different formulas, depending on whether it is a
> stock or an ETF or a mutual fund. These appear to grab letter rating
> and recommendation for stocks:
>
> =TRIM(LEFT(RCHGetWebData("http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","LetterGradeRating",,LEN("lettergraderating")+3),5))
> =TRIM(SUBSTITUTE(LEFT(RCHGetWebData("http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","CurrentRating"":",,LEN("currentrating")+3),4),"""",""))
>
> These appear to grab letter rating and recommendation for ETFs and mutual funds:
>
> =TRIM(SUBSTITUTE(LEFT(RCHGetWebData("http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","InvestmentRating",,LEN("investmentrating")+3),2),"""",""))
> =TRIM(SUBSTITUTE(LEFT(RCHGetWebData("http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","recommendation"":",,LEN("recommendation")+3),4),"""",""))
>
> I only looked at a handful of ticker symbols, so I'm not sure how well
> they work overall:
>
> VLO C+ Hold
> IBM A- Buy
> SPY B- BUY
> GCS B+ BUY
> VFINX C HOLD
>
> On 8/20/08, garyhartling <Gary.Hartling@...> wrote:
> > Is there any way to retrieve the recommendation from TheStreet ETF
> > Screener given a single ticker? Here's the website:
> >
> > http://www.thestreet.com/screener/index.html?src=ratingsindex&tab=1
> >
> > I enter the ticker "GCS" and get a "B [Buy]" result, which is what I
> > want to capture.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3b.

Re: TheStreet ETF Screener

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

Wed Jun 20, 2012 10:33 am (PDT)

They work fine for me.

However, I do have trouble entering the ACN ticker symbol, because EXCEL's
auto-correct feature wants to change it to CAN...

On Wed, Jun 20, 2012 at 9:21 AM, carmine288 <carmine.nicoletta@gmail.com>wrote:

> Why these formulas don't work for ticker ACN?
>
> --- In smf_addin@yahoogroups.com, "Randy Harmelink" <rharmelink@...>
> wrote:
> >
> > It appears you need different formulas, depending on whether it is a
> > stock or an ETF or a mutual fund. These appear to grab letter rating
> > and recommendation for stocks:
> >
> > =TRIM(LEFT(RCHGetWebData("
> http://ratings.thestreet.com/tools/inc/basicjs/
> "&A1&".js","LetterGradeRating",,LEN("lettergraderating")+3),5))
> > =TRIM(SUBSTITUTE(LEFT(RCHGetWebData("
> http://ratings.thestreet.com/tools/inc/basicjs/
> "&A1&".js","CurrentRating"":",,LEN("currentrating")+3),4),"""",""))
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

4a.

Re: GetWebData can't update data timely

Posted by: "bobc94595" conlonrc@comcast.net   bobc94595

Wed Jun 20, 2012 7:21 pm (PDT)

Thanks, Randy. Just for information, how and where do I get the name of the macro to be typed into the dropdown box mentioned in the messages for forced recalculation?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> If you're getting a #NAME? result, in a new worksheet, on:
>
> =RCHGetElementNumber("Version")
>
> ...then you most probably don't have the add-in activated as an add-in.
>
> F9 and Ctrl+Alt+F9 will not update extraction of data from web pages, only
> those add-in functions that use CSV files. You need to use the
> smfForceRecalculation macro (see the LINKS area of the group for more info).
>
> On Mon, Jun 18, 2012 at 8:46 PM, bobc94595 <conlonrc@...> wrote:
>
> > Randy, I need some help. All my spreadsheets, which worked perfectly last
> > week with the latest Works-In-Progress listing
> > (RCH_Stock_Market_Functions-2.1.2012.04.07.zip) , now return #NAME?
> > Even a new spreadsheet with only the formula ,
> > RCHGetElementNumber("Version") , gets that response.
> > When I open a sheet, and highlight a cell, the formula, for example, might
> > be
> > 'C:\Program Files\SMF
> > Add-In\RCH_Stock_Market_Functions.xla'!RCHGetYahooQuotes(B16,"y")/100 but
> > when I do a global search and replace (remove) of 'C:\Program Files\SMF
> > Add-In\RCH_Stock_Market_Functions.xla'! I get the same result.
> > I try to refresh with F9, CNTL + SHIFT R, CNTL + ALT F9 , but they
> > do nothing.
> > In the Internet, Browser Settings, where it says "Check for newer versions
> > of stored pages:", I have toggled the "Every time I visit the web page"
> > radio button.
> > For a forced recalculation, how do I get the name of the macro to be typed
> > into the dropdown box mentioned in the messages for forced recalculation?
> > I am using a PC with Win7 and Office 2010 and Excel 2010
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

4b.

Re: GetWebData can't update data timely

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

Wed Jun 20, 2012 7:38 pm (PDT)

That should have been covered in the LINKS item -- it's
smfForceRecalculation.

On Wed, Jun 20, 2012 at 7:21 PM, bobc94595 <conlonrc@comcast.net> wrote:

> Thanks, Randy. Just for information, how and where do I get the name of
> the macro to be typed into the dropdown box mentioned in the messages for
> forced recalculation?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

Recent Activity

•  74
  New Members

Visit Your Group

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

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

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