Function like this in Excel or Access? (warning: very specific & convoluted)

Factual Questions
1

Hold your pants, this is going to be convoluted:

In Excel, I have a table of ~100 stocks that update with price changes automatically. I’d like to detect patterns in groups of stocks when they undergo significant changes at the same time.

I’ve got a rough system set up now where I have a single keyword attached to a single field (say, “notebook_computers”) and then when a bunch of them move at once I can run a pivot table to see how many stocks changed under that keyword. (I first select out which stocks are significant movers according to certain criteria and label them “yes or no”). So then it’s simple enough to see in the pivot which stocks are “yes” and then further subdivide into “notebook_computer” and other single keywords.

(Deep breath.)

OK, what I’d like to do is create an unlimited number of keywords, I guess within a single field, and then see different combinations of major movers. Say, I still label some stocks “notebook_computer” but also “netbook” and “Dell”. Now, I could have combos of “notebook / netbook / Dell” but also “motherboard / netbook / Hewlett_Packard” or “netbook / processor / Atom”.

Would it be possible, especially using Excel or Access, to show how many stocks are “yes” and match an individual keyword “Dell” or individual keyword “netbook”? I could do a simple filter thingie on the original dataset, but that would be too time-consuming to make it worthwhile and further wouldn’t easily allow me to compare and rank the importance of keywords side by side. So something that lines up in a single table like my pivot method below would be best.

Does that make sense? If you can help me with this question, I’ll be happy to include you in my daily e-mails (assuming you’re interested in Asia).

Much appreciated! – Koxinga

2

Umm…My fault on this, but I know I’m not completely grasping the entire issue.

Having said that, in general I think that some of the capabilities in a relational database might enable you to do what (I think) you want. In Excel, I just don’t know.

But let’s say you have a table with the stock name, price, and so on. Now, in another table, you have the stock (Your key field) and another column (field) with the label. In that same secondary table, you can have as many combinations of the stock and a label name as you want. Example:

STOCK LABEL

DELL NOTEBOOK
DELL NETBOOK
DELL DESKTOP
HP DESKTOP
HP NOTEBOOK

and so on.

I would think that perhaps doing a query with a table join of the main table and this one would allow you the flexibility to do what you say.

I’ve avoided any SQL code examples just trying to keep it at a more-or-less conceptual level.

I apologize if I’ve not really understood what you want do do, but there’s lots of geniuses here and I’m sure one of them will come in with a better approach soon.

Edited to add, I know this is outside of Excel but you could export/import the resulting data although this may be more effort than would be worthwhile. Anyway, just a thought.

3

I agree that this is more suitable for a database. Can you import the information into MS Access?