lookup stock symbol on worksheet and return summary data

[Greg Purnell]

My friend tried to stump me, but he didn't say I couldn't use you guys to
find an answer...so here it is:

He has this summary data on one worksheet (but with MUCH more data
columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG
Enter symbol here

-2.24%
Returns the average


obviously, the formula he has in the lower box (where result populated
is -2.24%) is: =AVERAGE(Data!3:3)

Very basic, I know. So I called him after he sent the email to find out why
he's making a mountain out of a molehill. Obviously he could summarize the
data on this worksheet simply by calculating the averages on the source
worksheet and just using the =Cell() formula to return the results in a more
readable format.

He does, however, (and as I suspected) want to bring in a lot more than just
averages (SDs, variances, etc., etc.). Basically, he wants to type in a
symbol and have it return a bunch of data. He has it figured out one way
with an array formula, but with over 1000 symbols, and market data dating
back to early 2006, it is obvious he has way too much data. He did, of
course, tell me that the array formulae did take forever, so on to plan B.

I have some ideas, but I have other stuff I need to be working on, so I just
figured I'd come to the place where I've always been helped with Excel
(immensly!).

Basically, he just wants to be able to type in a symbol (a market ticker
symbol, but not for regular stocks, they deal with closed-end funds) - and
have it return some summary data. As I said before, he's starting with
"averages" but wants to apply the formula that I (well, "we") come up with
in order to return other types of data.

I did tell him right off the bat that this is an obvious and definite
application for Microsoft Access or SQL Server (or any DB app.), to which he
agreed. But his company is just a 2 man operation, and he doesn't have the
time to learn how to develop and implement one (a db), nor the funds to have
somebody else do so.

Also, if you have any comments, every time I bring up how he should be doing
this stuff on a database, he brings up "Filemaker Pro" - does anyone
professionally use that app.?

Thank you very much in advance all. You guys and gals have been absolutely
WONDERFUL with helping me with Excel over the years!! I really appreciate
it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net

 

[T. Valko]

Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff

 

[Greg Purnell]

Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg

 

[T. Valko]

Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate
pivot tables!

Biff

 

[Greg Purnell]

Thanks Biff. I'm with you on the Pivotable thing, but I think that may even
be the wrong application.

We need something that you can input (request/query) an existing worksheet,
and have information from that [searched] worksheet returned, and based on
pre-written formulae interpret and present the summary data in another
worksheet. It is basically (totally if you ask me) a database application
and request, however as I explained below, that is not an option we can
explore right now.

Do you think I might get any more replies to this thread, or do you think
some of the other experts might see that you have replied and might leave it
alone?

If so, and you think this one might be dead, do you have any advice on how I
could re-post or "bump" it?

Thanks again,
Greg

 

[T. Valko]

The way I see it is you want something totaly automated. Formulas are totaly
automated but you have to write the formulas first. A pivot table just saves
you from having to write formulas but is not totaly automated (although it
could be made so with some programming).

It sounds like you need to develop an application. An application is very
specific to its task. An application is also something that is done by a
professional developer and almost always involves complex programming.

I'm pretty sure that any other replies you get will basically be the same
things I've suggested. You might get someone who can guide you through on
how to setup a pivot table. So, just hang around for a day or two and see if
you get any more replies. If not, you can always repost (start a brand new
thread) and get a fresh set of eyes on your problem.

Biff

Thanks Biff. I'm with you on the Pivotable thing, but I think that may
even be the wrong application.

We need something that you can input (request/query) an existing
worksheet, and have information from that [searched] worksheet returned,
and based on pre-written formulae interpret and present the summary data
in another worksheet. It is basically (totally if you ask me) a database
application and request, however as I explained below, that is not an
option we can explore right now.

Do you think I might get any more replies to this thread, or do you think
some of the other experts might see that you have replied and might leave
it alone?

If so, and you think this one might be dead, do you have any advice on how
I could re-post or "bump" it?

Thanks again,
Greg

Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate
pivot tables!

Biff

 

[Guest]

This is pretty simple to do in Access.

Filemaker Pro, I believe, is a program for the Macintosh that also allows
people to build simple dbs.

So does your friend use a Mac or a PC? If it's a Mac he needs Filemaker
Pro, else he needs Access. SQL Server would be overkill for this application.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

The way I see it is you want something totaly automated. Formulas are totaly
automated but you have to write the formulas first. A pivot table just saves
you from having to write formulas but is not totaly automated (although it
could be made so with some programming).

It sounds like you need to develop an application. An application is very
specific to its task. An application is also something that is done by a
professional developer and almost always involves complex programming.

I'm pretty sure that any other replies you get will basically be the same
things I've suggested. You might get someone who can guide you through on
how to setup a pivot table. So, just hang around for a day or two and see if
you get any more replies. If not, you can always repost (start a brand new
thread) and get a fresh set of eyes on your problem.

Biff

Thanks Biff. I'm with you on the Pivotable thing, but I think that may
even be the wrong application.

We need something that you can input (request/query) an existing
worksheet, and have information from that [searched] worksheet returned,
and based on pre-written formulae interpret and present the summary data
in another worksheet. It is basically (totally if you ask me) a database
application and request, however as I explained below, that is not an
option we can explore right now.

Do you think I might get any more replies to this thread, or do you think
some of the other experts might see that you have replied and might leave
it alone?

If so, and you think this one might be dead, do you have any advice on how
I could re-post or "bump" it?

Thanks again,
Greg

Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate
pivot tables!

Biff

Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or
something and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2
columns for examples' sake.

Any other ideas?

Thanks,

Greg


Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff

My friend tried to stump me, but he didn't say I couldn't use you guys
to find an answer...so here it is:

He has this summary data on one worksheet (but with MUCH more data
columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG
Enter symbol here

-2.24%
Returns the average


obviously, the formula he has in the lower box (where result populated
is -2.24%) is: =AVERAGE(Data!3:3)

Very basic, I know. So I called him after he sent the email to find
out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the
averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.

He does, however, (and as I suspected) want to bring in a lot more
than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it
figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way
too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.

I have some ideas, but I have other stuff I need to be working on, so
I just figured I'd come to the place where I've always been helped
with Excel (immensly!).

Basically, he just wants to be able to type in a symbol (a market
ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's
starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data.

I did tell him right off the bat that this is an obvious and definite
application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he
doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, if you have any comments, every time I bring up how he should be
doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.?

Thank you very much in advance all. You guys and gals have been
absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net

 

[Guest]

Actually, re Filemaker: it is apparently available for both a Mac and
Windows. See: http://en.wikipedia.org/wiki/FileMaker

Access is only available for Windows.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

This is pretty simple to do in Access.

Filemaker Pro, I believe, is a program for the Macintosh that also allows
people to build simple dbs.

So does your friend use a Mac or a PC? If it's a Mac he needs Filemaker
Pro, else he needs Access. SQL Server would be overkill for this application.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

The way I see it is you want something totaly automated. Formulas are totaly
automated but you have to write the formulas first. A pivot table just saves
you from having to write formulas but is not totaly automated (although it
could be made so with some programming).

It sounds like you need to develop an application. An application is very
specific to its task. An application is also something that is done by a
professional developer and almost always involves complex programming.

I'm pretty sure that any other replies you get will basically be the same
things I've suggested. You might get someone who can guide you through on
how to setup a pivot table. So, just hang around for a day or two and see if
you get any more replies. If not, you can always repost (start a brand new
thread) and get a fresh set of eyes on your problem.

Biff

Thanks Biff. I'm with you on the Pivotable thing, but I think that may
even be the wrong application.

We need something that you can input (request/query) an existing
worksheet, and have information from that [searched] worksheet returned,
and based on pre-written formulae interpret and present the summary data
in another worksheet. It is basically (totally if you ask me) a database
application and request, however as I explained below, that is not an
option we can explore right now.

Do you think I might get any more replies to this thread, or do you think
some of the other experts might see that you have replied and might leave
it alone?

If so, and you think this one might be dead, do you have any advice on how
I could re-post or "bump" it?

Thanks again,
Greg


Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate
pivot tables!

Biff

Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or
something and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2
columns for examples' sake.

Any other ideas?

Thanks,

Greg


Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff

My friend tried to stump me, but he didn't say I couldn't use you guys
to find an answer...so here it is:

He has this summary data on one worksheet (but with MUCH more data
columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG
Enter symbol here

-2.24%
Returns the average


obviously, the formula he has in the lower box (where result populated
is -2.24%) is: =AVERAGE(Data!3:3)

Very basic, I know. So I called him after he sent the email to find
out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the
averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.

He does, however, (and as I suspected) want to bring in a lot more
than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it
figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way
too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.

I have some ideas, but I have other stuff I need to be working on, so
I just figured I'd come to the place where I've always been helped
with Excel (immensly!).

Basically, he just wants to be able to type in a symbol (a market
ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's
starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data.

I did tell him right off the bat that this is an obvious and definite
application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he
doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, if you have any comments, every time I bring up how he should be
doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.?

Thank you very much in advance all. You guys and gals have been
absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net