Summarize Multiple Records Based on Identifer
I'm looking for a way to crunch (CSV/Excel format) spreadsheets I download from my broker. I built an FMP10/Macintosh database to import the spreadsheets and have used it countless times. The problem is that each stock trade transaction, being in a large dollar amount, is broken down into dozens of transactions on the market floor and that makes it really hard to keep a neat journal. For instance, if I sell 400 shares of Apple (AAPL), the CSV download might break that down into 40 sales of Apple, each amounting to about ten shares. I call these "transaction segments" since each is just a tiny splinter of the once-simple transaction. It would be really neat if my broker (Scottrade) would just journalize it on the CSV document as "sold 400 shares" instead of splintering each transaction into dozens of transactions resulting in dozens of records per transaction.
Each transaction segment (part of the transaction) is one record when the CSV is imported into my FMP DB. I have a vague concept that I might need to give a special alpha-numeric identifier manually so that each segment in one transaction has the same identifier. For instance, If I sold 400 shares of Apple and the CSV has 40 records showing that I sold ten shares in each record, I could paste a custom identifier such as "2010MR19-1" to each and every of those particular records. Then I know that all records bearing that identifier refer to one specific transaction.
I think I might need some sort of formula or means to make every record bearing a matching identifier to aggregate as a total representing the values of one single stock trade. And I want this to happen after I enter a custom identifier to each record without searching/sorting. For instance, all records bearing the identifier "2010MR19-1" would generate a summary for those records, representing one single stock trade.
Curse Scottrade for not providing an order number on each trade record shown on their CSV!!!! Obviously I can sort transactions by date, stock symbol and by other criteria and I'm getting totals of these splintered transactions this way to reconstruct the values of each single transaction. But having to do a search, sort and summary to reconstruct each and every trade is a daunting task at tax time. What a mess! My broker also provides simplified totals of buys/sells for each stock. But these are useless for long term/short term capitol gains recordkeeping and other details that active traders would want.
Are there any Scottrade clients or FMPro users out there who understand this problem and have found a more automated means to crunch these splintered befuddling idiotic Scottrade transaction records in FMPro?