Stock Symbol Column comparison

M

Mike

Please help. I download stock symbol EOD (End of Day) data into Excel 2000.
Columns are titled Symbol, Date, Close, Volume. I am trying to compare the
closing price and volume columns for 2, 3, and 5 consecutive days. However,
for example Nasdaq, on one day contains 3, 340 symbols, the next day 3, 352
symbols. I would like to compare columns and retain in another column those
symbols that are common. Would appreciate any help possible. Thanks in
advance.
 
A

Aqib Rizvi

Best way would be to keep all unique symbols on a different sheet in a
'Table', write a lookup, and compare based on those kept in your
table.
Aqib Rizvi
 
H

Harlan Grove

Don Guillett said:
Will this work, copied down
=IF(COUNTIF(G:G,H:H)>0,G1,"")
....

It'd recalc very slowly. This alternative would recalc more quickly.

=IF(COUNT(MATCH(G1,H:H,0)),G1,"")
 
H

Harlan Grove

Don Guillett said:
Actually, my formula was wrong anyway
=IF(COUNTIF(H:H,G1)>0,G1,"")
....

It's still ill-conceived. Your COUNTIF call will ALWAYS go through all of
column H because the premise of COUNTIF is that there could be multiple
instances of G1 in H:H and all of them should be counted. The alternative

COUNT(MATCH(G1,H:H,0))

will stop when it finds the first instance of G1 in H:H. If this formula
would be filled down a column, all such COUNTIF formulas will do a lot more
unnecessary work than the same number of COUNT(MATCH(..)) formulas.
 
M

Mike

Aqib Rizvi said:
Best way would be to keep all unique symbols on a different sheet in a
'Table', write a lookup, and compare based on those kept in your
table.
Aqib Rizvi

Thanks Aqib
Mike
 
M

Mike

Don Guillett said:
Harlan, I wasn't arguing with you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


Hello, all. I really appreciate all the inputs. I am really a novice in Excel, please tell me which column to put formula (for example, if I have 4 columns ((symbol, date, close, volume) and two days this would take up to Column H)). Thanks again
Mike from Panama
 
M

Mike

Mike said:
Please help. I download stock symbol EOD (End of Day) data into Excel 2000.
Columns are titled Symbol, Date, Close, Volume. I am trying to compare the
closing price and volume columns for 2, 3, and 5 consecutive days. However,
for example Nasdaq, on one day contains 3, 340 symbols, the next day 3, 352
symbols. I would like to compare columns and retain in another column those
symbols that are common. Would appreciate any help possible. Thanks in
advance.

Thank you very much to all of you for your inputs. It helped very much. I
am a novice with Excel, but I used Harlan's after figuring out where to put
it and it worked, and it also selected those symbols that were common for two
days of Nasdaq EOD download and did it very fast. I can now go on and look
for "runners". Those stocks which go up in price and volume for 2, 3, and 4
days consecutive.

Again, appreciate all inputs........ Mike Davis in Panama
 
M

Mike

Mike said:
Please help. I download stock symbol EOD (End of Day) data into Excel 2000.
Columns are titled Symbol, Date, Close, Volume. I am trying to compare the
closing price and volume columns for 2, 3, and 5 consecutive days. However,
for example Nasdaq, on one day contains 3, 340 symbols, the next day 3, 352
symbols. I would like to compare columns and retain in another column those
symbols that are common. Would appreciate any help possible. Thanks in
advance.

New post: I was a bit premature. Yes, the formula that I tried that Harlan
provided worked. However, now the resulting column has a blank cell at
Nasdaq company number 219 (took that long to find a difference). I'm using
old (7-2 and 7-3) data, but not important. Now, my question is how can I
transfer the price and volume info in columns C, D and G, H to Column K with
formula of Harlan in Column J. Columns contain A-Symbol B-Date 20070702
C-Close D-Volume E-Symbol F-Date 20070703 G-Close H-Volume I-blank J-Formula
Appreciate inputs.
Mike in Panama
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top