Jumat, 05 Agustus 2011

[smf_addin] Digest Number 1909[1 Attachment]

Messages In This Digest (23 Messages)

1a.
smfGetTagContents From: Ron Spruell
1b.
Re: smfGetTagContents - never mind From: hashky
2a.
Re: Downloading financial statements from AdvFn From: bushpilote
2b.
Re: Downloading financial statements from AdvFn From: dguillett1
3a.
Re: downloading free bulk historical stock data From: ciroverdi77
3b.
Re: downloading free bulk historical stock data From: Randy Harmelink
4a.
Macro problems? From: Stephen Cole
4b.
Re: Macro problems? From: Randy Harmelink
4c.
Re: Macro problems? From: Stephen Cole
4d.
Re: Macro problems? From: Randy Harmelink
5a.
Re: IF and AND formula From: ROBERT GAUER
5b.
Re: IF and AND formula From: James Nylen
5c.
Re: IF and AND formula From: Ron
5d.
How to pull data from SEC Form 4 From: Au
5e.
Re: IF and AND formula From: Ron
5f.
Re: How to pull data from SEC Form 4 From: Randy Harmelink
5g.
Re: IF and AND formula From: Sill. Micheal
5h.
Re: IF and AND formula From: Sill. Micheal
5i.
Re: IF and AND formula From: Ron
5j.
Re: IF and AND formula From: Sill. Micheal
6a.
Competitor Listing From: Mike
6b.
Re: Competitor Listing From: Randy Harmelink
6c.
Re: Competitor Listing From: Mike Gregg

Messages

1a.

smfGetTagContents

Posted by: "Ron Spruell" hashky@yahoo.com   hashky

Thu Aug 4, 2011 4:12 am (PDT)

[Attachment(s) from Ron Spruell included below]

Randy -

I would like to pull the 90-Day Average Volume from the attached page.  I have made several unsuccessful trys.  Can it be done?  If it can be done would you correct my equation?

=smfGetTagContent("http://eresearch.fidelity.com/eresearch/evaluate/quote.jhtml?symbols="&A2&,"a",0,"&90-Day Average Volume=")

Ron

Attachment(s) from Ron Spruell

1 of 1 Photo(s)

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

Re: smfGetTagContents - never mind

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

Thu Aug 4, 2011 4:38 am (PDT)



I found the same info under KS in Yahoo using RCHGetElementNumber.

Sorry,
Ron

--- In smf_addin@yahoogroups.com, Ron Spruell <hashky@...> wrote:
>
> Randy -
>
> I would like to pull the 90-Day Average Volume from the attached page.  I have made several unsuccessful trys.  Can it be done?  If it can be done would you correct my equation?
>
> =smfGetTagContent("http://eresearch.fidelity.com/eresearch/evaluate/quote.jhtml?symbols="&A2&,"a",0,"&90-Day Average Volume=")
>
> Ron
>

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

Re: Downloading financial statements from AdvFn

Posted by: "bushpilote" bushpilote@yahoo.ca   bushpilote

Thu Aug 4, 2011 5:59 am (PDT)



Made some progress but still not quite there. However, your comment was extremely helpful in that it eliminated the coding for the URL/SYMBOL and AdvFn.com pulling a Morningstar as possible causes for the macro not working.

My macro writing skills are limited but thanks to macro recorder and Google search have managed to put together some effective macros. This one is a challenge.

Just for the record, I'm working on this for pesonal and not professional or business reasons. Simply put I'm not making any money off of it.

Thanks again for weighing in.

PS Randy, thanks for indulging me on this topic. In hindsight my question should have been directed elsewhere.

--- In smf_addin@yahoogroups.com, "dguillett1" <dguillett1@...> wrote:
>
> So how is it working for you now?
>
> From: bushpilote
> Sent: Wednesday, August 03, 2011 8:41 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Re: Downloading financial statements from AdvFn
>
>
> Thanks to both of you for the feedback.
>
> --- In mailto:smf_addin%40yahoogroups.com, "dguillett1" <dguillett1@> wrote:
> >
> >
> > I have an external query macro that uses
> > With ss.QueryTables(1)
> > .Connection = _
> > "URL;http://www.advfn.com/p.php?pid=financials&btn=s_ok&mode=&symbol=" & symbol & "&s_ok=OK"
> > Yours also worked
> > "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> > to get this
> > INDICATORS
> > quarter end date 1994/03 1994/06 1994/09 1994/12 1995/03
> > date preliminary data loaded 0000-00-00 0000-00-00 0000-00-00 0000-00-00 4/14/1995
> > earnings period indicator N F Q S N
> > quarterly indicator 3 4 1 2 3
> > basic earnings indicator N N N N N
> > template indicator
> > preliminary full context ind N N N N N
> > projected fiscal year date 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00
> > number of months last report period 3 3 3 3 3
> > INCOME STATEMENT
> > operating revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
> > total revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
> >
> > From: bushpilote
> > Sent: Tuesday, August 02, 2011 2:50 PM
> > To: mailto:smf_addin%40yahoogroups.com
> > Subject: [smf_addin] Downloading financial statements from AdvFn
> >
> >
> > Hi Randy,
> > I'm trying to download financial statements from AdvFn.com through an excel macro (I am familiar with the smfGetAdvFNElement-Template-All-Line-Items.xls template).
> > Can you or anyone else on this board help me understand why the following critical lines of coding are not working:
> >
> > "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> > , Destination:=Range("$B$1"))
> > .Name = _
> > "p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70"
> >
> > Thanks.
> >
>

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

Re: Downloading financial statements from AdvFn

Posted by: "dguillett1" dguillett1@gmail.com   donaldb36

Thu Aug 4, 2011 6:36 am (PDT)



If desired, send me duillett1 @gmail.com your file and I’ll take a look

From: bushpilote
Sent: Thursday, August 04, 2011 7:59 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Downloading financial statements from AdvFn

Made some progress but still not quite there. However, your comment was extremely helpful in that it eliminated the coding for the URL/SYMBOL and AdvFn.com pulling a Morningstar as possible causes for the macro not working.

My macro writing skills are limited but thanks to macro recorder and Google search have managed to put together some effective macros. This one is a challenge.

Just for the record, I'm working on this for pesonal and not professional or business reasons. Simply put I'm not making any money off of it.

Thanks again for weighing in.

PS Randy, thanks for indulging me on this topic. In hindsight my question should have been directed elsewhere.

--- In mailto:smf_addin%40yahoogroups.com, "dguillett1" <dguillett1@...> wrote:
>
> So how is it working for you now?
>
> From: bushpilote
> Sent: Wednesday, August 03, 2011 8:41 AM
> To: mailto:smf_addin%40yahoogroups.com
> Subject: [smf_addin] Re: Downloading financial statements from AdvFn
>
>
> Thanks to both of you for the feedback.
>
> --- In mailto:smf_addin%40yahoogroups.com, "dguillett1" <dguillett1@> wrote:
> >
> >
> > I have an external query macro that uses
> > With ss.QueryTables(1)
> > .Connection = _
> > "URL;http://www.advfn.com/p.php?pid=financials&btn=s_ok&mode=&symbol=" & symbol & "&s_ok=OK"
> > Yours also worked
> > "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> > to get this
> > INDICATORS
> > quarter end date 1994/03 1994/06 1994/09 1994/12 1995/03
> > date preliminary data loaded 0000-00-00 0000-00-00 0000-00-00 0000-00-00 4/14/1995
> > earnings period indicator N F Q S N
> > quarterly indicator 3 4 1 2 3
> > basic earnings indicator N N N N N
> > template indicator
> > preliminary full context ind N N N N N
> > projected fiscal year date 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00
> > number of months last report period 3 3 3 3 3
> > INCOME STATEMENT
> > operating revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
> > total revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
> >
> > From: bushpilote
> > Sent: Tuesday, August 02, 2011 2:50 PM
> > To: mailto:smf_addin%40yahoogroups.com
> > Subject: [smf_addin] Downloading financial statements from AdvFn
> >
> >
> > Hi Randy,
> > I'm trying to download financial statements from AdvFn.com through an excel macro (I am familiar with the smfGetAdvFNElement-Template-All-Line-Items.xls template).
> > Can you or anyone else on this board help me understand why the following critical lines of coding are not working:
> >
> > "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> > , Destination:=Range("$B$1"))
> > .Name = _
> > "p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70"
> >
> > Thanks.
> >
>

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

Re: downloading free bulk historical stock data

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

Thu Aug 4, 2011 8:07 am (PDT)



Thanks for getting back to me, but:

1) Being a former COBAL programmer, all I need is a simple ASCII or text file of ALL stock tickers of ALL markets with data elements separated by a comma, or space. I then unstring the data elements via my program -- I do not need to use Excel or its machinations, and found it unnecessarily complicated.

2) I would expect to simply define the data elements and where to store it.

Can it not be that simple??? Ciro

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

Re: downloading free bulk historical stock data

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

Thu Aug 4, 2011 8:09 am (PDT)



Sorry, but that's outside of the scope of this group...

On Thu, Aug 4, 2011 at 7:48 AM, ciroverdi77 <ciroverdi77@yahoo.com> wrote:

> Thanks for getting back to me, but:
>
> 1) Being a former COBAL programmer, all I need is a simple ASCII or text
> file of ALL stock tickers of ALL markets with data elements separated by a
> comma, or space. I then unstring the data elements via my program -- I do
> not need to use Excel or its machinations, and found it unnecessarily
> complicated.
>
> 2) I would expect to simply define the data elements and where to store
> it.
>
> Can it not be that simple??? Ciro
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4a.

Macro problems?

Posted by: "Stephen Cole" scole@colenetlaw.com   subrostud

Thu Aug 4, 2011 8:17 am (PDT)



I followed directions, including find and replace all.

Entered a symbol, and received message:

Cannot run the macro “C:\program files\SMF… The macro may not be available or all macros may be disabled.

I enabled macros. I checked the file location of the add in and it appears to be correct.

Any ideas?

Stephen

Stephen N. Cole

ofc: 916-376-0486

fax: 208-693-2460

cel: 916-869-1651

"In a democracy, we have always had to worry about the ignorance of the uneducated. Today we have to worry about the ignorance of people with college degrees." Thomas Sowell.
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
4b.

Re: Macro problems?

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

Thu Aug 4, 2011 8:25 am (PDT)



With an add-in installed, there is no reason to enable macros. It sounds
like your find and replace was either on the wrong string or didn't get all
occurrences.

On Thu, Aug 4, 2011 at 8:13 AM, Stephen Cole <scole@colenetlaw.com> wrote:

>
> I followed directions, including find and replace all.****
>
> Entered a symbol, and received message:****
>
> Cannot run the macro “C:\program files\SMF… The macro may not be available
> or all macros may be disabled.****
>
> ** **
>
> I enabled macros. I checked the file location of the add in and it appears
> to be correct.****
>
> ** **
>
> Any ideas? ****
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
4c.

Re: Macro problems?

Posted by: "Stephen Cole" scole@colenetlaw.com   subrostud

Thu Aug 4, 2011 8:56 am (PDT)



How do I disable macros (now that I’ve enabled them!) in Excel 07?



Stephen

Stephen N. Cole

ofc: 916-376-0486

fax: 208-693-2460

cel: 916-869-1651



"In a democracy, we have always had to worry about the ignorance of the uneducated. Today we have to worry about the ignorance of people with college degrees." Thomas Sowell.



From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Thursday, August 04, 2011 8:25 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Macro problems?





With an add-in installed, there is no reason to enable macros. It sounds like your find and replace was either on the wrong string or didn't get all occurrences.

On Thu, Aug 4, 2011 at 8:13 AM, Stephen Cole <scole@colenetlaw.com> wrote:



I followed directions, including find and replace all.

Entered a symbol, and received message:

Cannot run the macro “C:\program files\SMF… The macro may not be available or all macros may be disabled.



I enabled macros. I checked the file location of the add in and it appears to be correct.



Any ideas?







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

Re: Macro problems?

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

Thu Aug 4, 2011 8:59 am (PDT)



If you globally enabled them, you just go through the same process you did
before and choose a different security option. I'd have to Google it to find
a description of the process, which you can do just as easily as I can.

If you enabled them just for the workbook, the enabling goes away as soon as
you close the workbook.

On Thu, Aug 4, 2011 at 8:29 AM, Stephen Cole <scole@colenetlaw.com> wrote:

>
> How do I disable macros (now that I’ve enabled them!) in Excel 07?****
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5a.

Re: IF and AND formula

Posted by: "ROBERT GAUER" b_bgauer.56@verizon.net   bgauer

Thu Aug 4, 2011 9:18 am (PDT)



Let's put it into words:
IF C171 is greater than D171, AND D171 is positive, then change the sign of the difference between D171 and C171 and divide by D171. If the AND is not true, then use 1? END of IF

next IF:

----- Original Message -----
From: futants46and2
To: smf_addin@yahoogroups.com
Sent: Wednesday, August 03, 2011 8:49 PM
Subject: [smf_addin] IF and AND formula

Hey Randy,

I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.

=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))

Thank You,
Elliot.

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

Re: IF and AND formula

Posted by: "James Nylen" jnylen@gmail.com   jamesnylen

Thu Aug 4, 2011 9:27 am (PDT)



One obvious problem is that you have C1710 where I think you mean C171. I
suspect that your formula can be simplified a lot - for example (X-Y)*-1 is
just Y-X and X+(Y*-1) is just X-Y. I haven't checked your syntax though.

One thing that I find helps when you have complicated formulas is to split
the steps into multiple columns. This is also a good way to eliminate
redundant parts.

On Wed, Aug 3, 2011 at 9:49 PM, futants46and2 <futants46and2@yahoo.com>wrote:

> **
>
>
> Hey Randy,
>
> I know this is off topic, I have been trying to create an If and AND
> formula that has 3 arguments and a different outcome for each argument. I
> think my syntax is wrong, any help or guidance you can provide I will of
> course greatly appreciate. Below is the formula I constructed.
>
>
> =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
>
> Thank You,
> Elliot.
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5c.

Re: IF and AND formula

Posted by: "Ron" ronaldj309@cox.net   ronaldj309

Thu Aug 4, 2011 11:57 am (PDT)



Hopefully helpful hint:

To check the matching of parentheses, count up for a left paren and down for a right paren, as below

1 2 1 2 3 2 3 2 1 0
=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,

This shows that the end of the IF is below the 0. Not what you want, I think.

Ron Johnson

--- In smf_addin@yahoogroups.com, "ROBERT GAUER" <b_bgauer.56@...> wrote:
>
> Let's put it into words:
> IF C171 is greater than D171, AND D171 is positive, then change the sign of the difference between D171 and C171 and divide by D171. If the AND is not true, then use 1? END of IF
>
> next IF:
>
>
>
>
> ----- Original Message -----
> From: futants46and2
> To: smf_addin@yahoogroups.com
> Sent: Wednesday, August 03, 2011 8:49 PM
> Subject: [smf_addin] IF and AND formula
>
>
>
> Hey Randy,
>
> I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.
>
> =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
>
> Thank You,
> Elliot.
>

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

How to pull data from SEC Form 4

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Thu Aug 4, 2011 12:23 pm (PDT)



Hello Randy,
Is it possible to pull data from this page. 
http://www.sec.gov/cgi-bin/srch-edgar?text=company-name%3D%22Net+1+Ueps+Technologies%22+AND+form-type%3D%2810-q*+OR+10-k*+OR+4%29&first=1996&last=2011

http://www.sec.gov/Archives/edgar/data/1041514/000106299310003865/xslF345X03/form4.xml

regards
Anant
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5e.

Re: IF and AND formula

Posted by: "Ron" ronaldj309@cox.net   ronaldj309

Thu Aug 4, 2011 1:06 pm (PDT)



Try again with RTF. Hopefully the numeric characters are in the proper
positions now.
--- In smf_addin@yahoogroups.com, "Ron" <ronaldj309@...> wrote:
>
> Hopefully helpful hint:
>
> To check the matching of parentheses, count up for a left paren and
down for a right paren, as below
>
> 1 2 1 2 3
2 3 2 1 0
> =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,
>
> This shows that the end of the IF is below the 0. Not what you want,
I think.
>
> Ron Johnson
>
>
> --- In smf_addin@yahoogroups.com, "ROBERT GAUER" b_bgauer.56@ wrote:
> >
> > Let's put it into words:
> > IF C171 is greater than D171, AND D171 is positive, then change the
sign of the difference between D171 and C171 and divide by D171. If the
AND is not true, then use 1? END of IF
> >
> > next IF:
> >
> >
> >
> >
> > ----- Original Message -----
> > From: futants46and2
> > To: smf_addin@yahoogroups.com
> > Sent: Wednesday, August 03, 2011 8:49 PM
> > Subject: [smf_addin] IF and AND formula
> >
> >
> >
> > Hey Randy,
> >
> > I know this is off topic, I have been trying to create an If and
AND formula that has 3 arguments and a different outcome for each
argument. I think my syntax is wrong, any help or guidance you can
provide I will of course greatly appreciate. Below is the formula I
constructed.
> >
> >
=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171\
-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),\
3,0,)))
> >
> > Thank You,
> > Elliot.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5f.

Re: How to pull data from SEC Form 4

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

Thu Aug 4, 2011 1:24 pm (PDT)



I was able to extract data from both pages, using either RCHGetTableCell()
or smfGetTagContent() functions.

It's easy enough to try --- just do something like:

=smfGetTagContent(...URL...,"td",...counter...)

...and varying "...counter..." from 1 to n to see what is in the various
table cells on the web page.

On Thu, Aug 4, 2011 at 12:22 PM, Au <uanant@ymail.com> wrote:

>
> Is it possible to pull data from this page.
>
> http://www.sec.gov/cgi-bin/srch-edgar?text=company-name%3D%22Net+1+Ueps+Technologies%22+AND+form-type%3D%2810-q*+OR+10-k*+OR+4%29&first=1996&last=2011
>
>
> http://www.sec.gov/Archives/edgar/data/1041514/000106299310003865/xslF345X03/form4.xml
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5g.

Re: IF and AND formula

Posted by: "Sill. Micheal" futants46and2@yahoo.com   futants46and2

Thu Aug 4, 2011 1:35 pm (PDT)



Yes, thanks I did not realize that error. I will try writing it out formula by formula, and then joining them.

________________________________
From: James Nylen <jnylen@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, August 4, 2011 12:27 PM
Subject: Re: [smf_addin] IF and AND formula

 
One obvious problem is that you have C1710 where I think you mean C171.  I suspect that your formula can be simplified a lot - for example (X-Y)*-1 is just Y-X and X+(Y*-1) is just X-Y.  I haven't checked your syntax though.

One thing that I find helps when you have complicated formulas is to split the steps into multiple columns.  This is also a good way to eliminate redundant parts.

On Wed, Aug 3, 2011 at 9:49 PM, futants46and2 <futants46and2@yahoo.com> wrote:

> 
>Hey Randy,
>
>I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.
>
>=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
>
>Thank You,
>Elliot.
>
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5h.

Re: IF and AND formula

Posted by: "Sill. Micheal" futants46and2@yahoo.com   futants46and2

Thu Aug 4, 2011 1:47 pm (PDT)



I am not sure what you mean by counting the parentheses up and down, can you please explain? Exactly I am looking for the IF formula to show above 0 which I used D171>0 to define greater than 0?
For ease lets replace Actual for C171 and Estimate for D171  so the formula will be =IF(AND(actual>estimate,estimate>0),((estimate-actual)*(-1))/estimate),1,

Thanks,
Elliot.

________________________________
From: Ron <ronaldj309@cox.net>
To: smf_addin@yahoogroups.com
Sent: Thursday, August 4, 2011 4:06 PM
Subject: [smf_addin] Re: IF and AND formula

 
Try again with RTF.  Hopefully the numeric characters are in the proper positions now.

--- In smf_addin@yahoogroups.com, "Ron" <ronaldj309@...> wrote:
>
> Hopefully helpful hint:
>
> To check the matching of parentheses, count up for a left paren and down for a right paren, as below
>
>        1         2                                       1   2 3                  2   3  2  1          0
> =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,
>
> This shows that the end of the IF is below the 0. Not what you want, I think.
>
> Ron Johnson
>
>
> --- In smf_addin@yahoogroups.com, "ROBERT GAUER" b_bgauer.56@ wrote:
> >
> > Let's put it into words:
> > IF C171 is greater than D171, AND D171 is positive, then change the sign of the difference between D171 and C171 and divide by D171. If the AND is not true, then use 1? END of IF
> >
> > next IF:
> >
> >
> >
> >
> > ----- Original Message -----
> > From: futants46and2
> > To: smf_addin@yahoogroups.com
> > Sent: Wednesday, August 03, 2011 8:49 PM
> > Subject: [smf_addin] IF and AND formula
> >
> >
> >
> > Hey Randy,
> >
> > I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.
> >
> > =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
> >
> > Thank You,
> > Elliot.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5i.

Re: IF and AND formula

Posted by: "Ron" ronaldj309@cox.net   ronaldj309

Thu Aug 4, 2011 3:07 pm (PDT)



Regrettably I have not been able to align my count values with the If expression. The idea is that if you count (left to right) up by one for each left paren and down by one for each right paren, the end of the overall parenthetical expression is where the count reaches zero.
I'll try entering something again to illustrate (with underscores rather than spaces:
IF(ArgA>(ArgB+ArgC),RltA,(RltA+RltB) )
__1_____2_________1______2_________1_0
Hope this alignment comes across Ok

--- In smf_addin@yahoogroups.com, "Sill. Micheal" <futants46and2@...> wrote:
>
> I am not sure what you mean by counting the parentheses up and down, can you please explain? Exactly I am looking for the IF formula to show above 0 which I used D171>0 to define greater than 0?
> For ease lets replace Actual for C171 and Estimate for D171  so the formula will be =IF(AND(actual>estimate,estimate>0),((estimate-actual)*(-1))/estimate),1,
>
> Thanks,
> Elliot.
>
>
> ________________________________
> From: Ron <ronaldj309@...>
> To: smf_addin@yahoogroups.com
> Sent: Thursday, August 4, 2011 4:06 PM
> Subject: [smf_addin] Re: IF and AND formula
>
>
>  
> Try again with RTF.  Hopefully the numeric characters are in the proper positions now.
>
> --- In smf_addin@yahoogroups.com, "Ron" <ronaldj309@> wrote:
> >
> > Hopefully helpful hint:
> >
> > To check the matching of parentheses, count up for a left paren and down for a right paren, as below
> >
> >        1         2                                       1   2 3                  2   3  2  1          0
> > =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,
> >
> > This shows that the end of the IF is below the 0. Not what you want, I think.
> >
> > Ron Johnson
> >
> >
> > --- In smf_addin@yahoogroups.com, "ROBERT GAUER" b_bgauer.56@ wrote:
> > >
> > > Let's put it into words:
> > > IF C171 is greater than D171, AND D171 is positive, then change the sign of the difference between D171 and C171 and divide by D171. If the AND is not true, then use 1? END of IF
> > >
> > > next IF:
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: futants46and2
> > > To: smf_addin@yahoogroups.com
> > > Sent: Wednesday, August 03, 2011 8:49 PM
> > > Subject: [smf_addin] IF and AND formula
> > >
> > >
> > >
> > > Hey Randy,
> > >
> > > I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.
> > >
> > > =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
> > >
> > > Thank You,
> > > Elliot.
> > >
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
5j.

Re: IF and AND formula

Posted by: "Sill. Micheal" futants46and2@yahoo.com   futants46and2

Thu Aug 4, 2011 3:35 pm (PDT)



Thanks for that tip. I counted it and it netted out to 0 at the end of the formula so my count values are fine. Using the formula edits I was able to now have the formula return a 100% value which means my formula is not working.

I am thinking if I need to use a different conditional function rather than IF, AND? The formula I put together is to determine which mathematical formula is needed to solve for 3 different outcomes (there are 3 different formulas one for each outcome). I have 2 givens an Actual, and Estimate number, and I am trying to display the % difference of the actual vs. estimate as either a -, or + number. ie. if actual is 10, and estimate is 5 then the total will be displayed as 50%. I have the 3 separate formulas in order to reduce the (-) (+) confusions.

Thanks,
Elliot.

________________________________
From: Ron <ronaldj309@cox.net>
To: smf_addin@yahoogroups.com
Sent: Thursday, August 4, 2011 6:06 PM
Subject: [smf_addin] Re: IF and AND formula

 
Regrettably I have not been able to align my count values with the If expression. The idea is that if you count (left to right) up by one for each left paren and down by one for each right paren, the end of the overall parenthetical expression is where the count reaches zero.
I'll try entering something again to illustrate (with underscores rather than spaces:
IF(ArgA>(ArgB+ArgC),RltA,(RltA+RltB) )
__1_____2_________1______2_________1_0
Hope this alignment comes across Ok

--- In smf_addin@yahoogroups.com, "Sill. Micheal" <futants46and2@...> wrote:
>
> I am not sure what you mean by counting the parentheses up and down, can you please explain? Exactly I am looking for the IF formula to show above 0 which I used D171>0 to define greater than 0?
> For ease lets replace Actual for C171 and Estimate for D171  so the formula will be =IF(AND(actual>estimate,estimate>0),((estimate-actual)*(-1))/estimate),1,
>
> Thanks,
> Elliot.
>
>
> ________________________________
> From: Ron <ronaldj309@...>
> To: smf_addin@yahoogroups.com
> Sent: Thursday, August 4, 2011 4:06 PM
> Subject: [smf_addin] Re: IF and AND formula
>
>
>  
> Try again with RTF.  Hopefully the numeric characters are in the proper positions now.
>
> --- In smf_addin@yahoogroups.com, "Ron" <ronaldj309@> wrote:
> >
> > Hopefully helpful hint:
> >
> > To check the matching of parentheses, count up for a left paren and down for a right paren, as below
> >
> >        1         2                                       1   2 3                  2   3  2  1          0
> > =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,
> >
> > This shows that the end of the IF is below the 0. Not what you want, I think.
> >
> > Ron Johnson
> >
> >
> > --- In smf_addin@yahoogroups.com, "ROBERT GAUER" b_bgauer.56@ wrote:
> > >
> > > Let's put it into words:
> > > IF C171 is greater than D171, AND D171 is positive, then change the sign of the difference between D171 and C171 and divide by D171. If the AND is not true, then use 1? END of IF
> > >
> > > next IF:
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: futants46and2
> > > To: smf_addin@yahoogroups.com
> > > Sent: Wednesday, August 03, 2011 8:49 PM
> > > Subject: [smf_addin] IF and AND formula
> > >
> > >
> > >
> > > Hey Randy,
> > >
> > > I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.
> > >
> > > =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
> > >
> > > Thank You,
> > > Elliot.
> > >
> >
>

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

Competitor Listing

Posted by: "Mike" gmichaelj@yahoo.com   gmichaelj

Thu Aug 4, 2011 3:21 pm (PDT)




Hi Randy:

I made a file a couple of years ago that no longer works.

I could type in a ticker and get back general industry information, and
most imporantly, competitors ranked by market cap.

I'd attach the file, but don't see how to do it.

Anyway, here are some of the formulas:

cell C6 = 1

cell B7 = 4

cell C7
=IF(C$1="y",RCHGetTableCell("http://biz.yahoo.com/p/"&Industry&"mktd.htm\
l",C$6,">ROE",,,,$B7,"</table
<http://biz.yahoo.com/p/"&Industry&"mktd.html",C$6,">ROE",,,,$B7,">
",,"--"))

cell D7
=IF(C7="--","--",HYPERLINK("http://finance.yahoo.com/q?s="&SUBSTITUTE(MI\
D(C7,FIND("(",C7)+1,9),")",""),SUBSTITUTE(MID(C7,FIND("(",C7)+1,9
<http://finance.yahoo.com/q?s="&SUBSTITUTE(MID(C7,FIND("(",C7)+1,9),")",\
""),SUBSTITUTE(MID(C7,FIND("(",C7)+1,9> ),")","")))

cell E6 = 2

cell E7
=IF(E$1="y",RCHGetTableCell("http://biz.yahoo.com/p/"&Industry&"mktd.htm\
l",E$6,">ROE",,,,$B7,"</table
<http://biz.yahoo.com/p/"&Industry&"mktd.html",E$6,">ROE",,,,$B7,">
",,"--"))

cell F6 = 3

cell F7
=IF(F$1="y",RCHGetTableCell("http://biz.yahoo.com/p/"&Industry&"mktd.htm\
l",F$6,">ROE",,,,$B7,"</table
<http://biz.yahoo.com/p/"&Industry&"mktd.html",F$6,">ROE",,,,$B7,">
",,"--"))

Thank you very much

Mike

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

Re: Competitor Listing

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

Thu Aug 4, 2011 3:25 pm (PDT)



If you look at the Yahoo page you're trying to extract the data from, you'll
note that they are no longer populating their Industry Browser with
company-level data...

If you search the message archives, there was a thread within the last month
or so on getting competitor data from Google.

On Thu, Aug 4, 2011 at 3:12 PM, Mike <gmichaelj@yahoo.com> wrote:

> I made a file a couple of years ago that no longer works.
>
> I could type in a ticker and get back general industry information, and
> most imporantly, competitors ranked by market cap.
>
> I'd attach the file, but don't see how to do it.
>
> Anyway, here are some of the formulas:
>
> cell C6 = 1
>
> cell B7 = 4
>
> cell C7 =IF(C$1="y",RCHGetTableCell("
> http://biz.yahoo.com/p/"&Industry&"mktd.html",C$6,">ROE",,,,$B7,"</table
> ",,"--"))
>
> cell D7 =IF(C7="--","--",HYPERLINK("
> http://finance.yahoo.com/q?s="&SUBSTITUTE(MID(C7,FIND("(",C7)+1,9),")",""),SUBSTITUTE(MID(C7,FIND("(",C7)+1,9<http://finance.yahoo.com/q?s=%22&SUBSTITUTE%28MID%28C7,FIND%28%22%28%22,C7%29+1,9%29,%22%29%22,%22%22%29,SUBSTITUTE%28MID%28C7,FIND%28%22%28%22,C7%29+1,9>
> ),")","")))
>
> cell E6 = 2
>
> cell E7 =IF(E$1="y",RCHGetTableCell("
> http://biz.yahoo.com/p/"&Industry&"mktd.html",E$6,">ROE",,,,$B7,"</table
> ",,"--"))
>
> cell F6 = 3
>
> cell F7 =IF(F$1="y",RCHGetTableCell("
> http://biz.yahoo.com/p/"&Industry&"mktd.html",F$6,">ROE",,,,$B7,"</table
> ",,"--"))
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
6c.

Re: Competitor Listing

Posted by: "Mike Gregg" gmichaelj@yahoo.com   gmichaelj

Thu Aug 4, 2011 3:39 pm (PDT)



thank you

From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, August 4, 2011 6:25 PM
Subject: Re: [smf_addin] Competitor Listing

 
If you look at the Yahoo page you're trying to extract the data from, you'll note that they are no longer populating their Industry Browser with company-level data...

If you search the message archives, there was a thread within the last month or so on getting competitor data from Google.

On Thu, Aug 4, 2011 at 3:12 PM, Mike <gmichaelj@yahoo.com> wrote:

I made a file a couple of years ago that no longer works. 
>I could type in a ticker and get back general industry information, and most imporantly, competitors ranked by market cap.
>I'd attach the file, but don't see how to do it.  
>Anyway, here are some of the formulas:
>cell C6 = 1
>cell B7 = 4
>cell C7 =IF(C$1="y",RCHGetTableCell("http://biz.yahoo.com/p/"&Industry&"mktd.html",C$6,">ROE",,,,$B7,"</table",,"--"))
>cell D7 =IF(C7="--","--",HYPERLINK("http://finance.yahoo.com/q?s="&SUBSTITUTE(MID(C7,FIND("(",C7)+1,9),")",""),SUBSTITUTE(MID(C7,FIND("(",C7)+1,9),")","")))
>cell E6 = 2
>cell E7 =IF(E$1="y",RCHGetTableCell("http://biz.yahoo.com/p/"&Industry&"mktd.html",E$6,">ROE",,,,$B7,"</table",,"--"))
>cell F6 = 3
>cell F7 =IF(F$1="y",RCHGetTableCell("http://biz.yahoo.com/p/"&Industry&"mktd.html",F$6,">ROE",,,,$B7,"</table",,"--"))
>

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

Learn more now.

Reach customers

searching for you.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

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 03.55

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)