Stock Symbol Column comparison
 

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.

Stock Symbol Column comparison
 

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

Stock Symbol Column comparison
 

Will this work, copied down
=IF(COUNTIF(G:G,H:H)0,G1,"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike" wrote in message
...
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.

Stock Symbol Column comparison
 

"Don Guillett" wrote...
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,"")

Stock Symbol Column comparison
 

Actually, my formula was wrong anyway
=IF(COUNTIF(H:H,G1)0,G1,"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
"Don Guillett" wrote...
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,"")

Stock Symbol Column comparison
 

"Don Guillett" wrote...
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.

Stock Symbol Column comparison
 

Harlan, I wasn't arguing with you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
"Don Guillett" wrote...
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.

Stock Symbol Column comparison
 

"Aqib Rizvi" wrote:

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

Stock Symbol Column comparison
 

"Don Guillett" wrote:

Harlan, I wasn't arguing with you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
"Don Guillett" wrote...
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.


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

Stock Symbol Column comparison
 

"Mike" wrote:

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

Stock Symbol Column comparison
 

"Mike" wrote:

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