G
Greg Purnell
Dear All:
First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..
Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"
Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.
Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.
And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.
If someone could please point me in the right direction, I'd be much obliged.
Thank you very much for your time and efforts in this matter.
Best Regards:
Greg Purnell
Jgpurnell13 - at - Verizon.net
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HERE IS MY INITIAL POST:
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) (keep in mind I hid 100's of columns so avg is incorrect)
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 or variations thereof.
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, as a side note - 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.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIFF's INITIAL REPLY:
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 REPLY:
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..
Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"
Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.
Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.
And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.
If someone could please point me in the right direction, I'd be much obliged.
Thank you very much for your time and efforts in this matter.
Best Regards:
Greg Purnell
Jgpurnell13 - at - Verizon.net
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HERE IS MY INITIAL POST:
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) (keep in mind I hid 100's of columns so avg is incorrect)
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 or variations thereof.
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, as a side note - 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.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIFF's INITIAL REPLY:
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 REPLY:
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------