Home > Archive > Extreme Programming > May 2004 > counting things in a spreadsheet
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
counting things in a spreadsheet
|
|
| WMMorgan 2004-05-14, 1:30 pm |
| This looks like a good place to ask:
I am using a spreadsheet organize 15 days worth of stock market price
data.
Each days' worth of data (row: symbol in text, low price, high price),
in column form, is a bit different from every other days' column of
data, because a handful of stocks are added or dropped on an almost
daily basis. Each day has more than 7000 stocks.
Problem: how do I sort out and discard all the stocks that do NOT
appear in each and every of the 15 days?
Though MS Works Spreadsheet is grossly inadequate for the task, there
is a way to do it (but it's time-consuming, involving a lot of cutting
and pasting and SORT and a choice formula or two).
Some shareware spreadsheets have COUNTIF, but I can't get the COUNTIF
to reference text (symbol) from another cell for its value. (Method:
count each symbol's occurence in the entire 15 days' range of data.
Discard those stocks that occur less than 15 times.)
Any ideas?
| |
|
| Do you do VBA?
Easy enough to create a subroutine in Access or something similar...
The algorithm would go something like:
- create a separate table with the same fields as the spreadsheet, plus an
additional field called "counter".
- loop through the spreadsheet
- for each unique item, create an entry in the new table with the
counter field set to zero
- end loop
- loop through the new table
- for each item, count how many days it occurs in the spreadsheet (inner
loop)
- end loop
- grab all items in the new table that have a counter >= 15
It seems unwieldy, and there are probably more elegant ways to do this, but
it will run pretty quickly on a P4.
JRL
www.mitconsulting.ca
"WMMorgan" <mitcheroo@comcast.net> wrote in message
news:453b9939.0405140802.2ad28566@posting.google.com...
> This looks like a good place to ask:
>
> I am using a spreadsheet organize 15 days worth of stock market price
> data.
>
> Each days' worth of data (row: symbol in text, low price, high price),
> in column form, is a bit different from every other days' column of
> data, because a handful of stocks are added or dropped on an almost
> daily basis. Each day has more than 7000 stocks.
>
> Problem: how do I sort out and discard all the stocks that do NOT
> appear in each and every of the 15 days?
>
> Though MS Works Spreadsheet is grossly inadequate for the task, there
> is a way to do it (but it's time-consuming, involving a lot of cutting
> and pasting and SORT and a choice formula or two).
>
> Some shareware spreadsheets have COUNTIF, but I can't get the COUNTIF
> to reference text (symbol) from another cell for its value. (Method:
> count each symbol's occurence in the entire 15 days' range of data.
> Discard those stocks that occur less than 15 times.)
>
> Any ideas?
|
|
|
|
|