melfinance
New Member
- Joined
- Jan 20, 2005
- Messages
- 7
I have a spreadsheet the contains the prices of 20,000 securities at the end of month in two different months: say November and December.
Each security is listed by its cusip (identifier), the account that its in and the end of month price for the total account holdings. So for two months there would be three columns of data for each month for a total of 6 columns.
What I have to do is find the average price between the two months.
I usually sort by account and then when both accounts have the same security I just add them and divide by two. The problem is that every month some accounts no longer exist or no longer hold the security. Also some accounts get added and some securities get added. I need a way for excel to look and see if there is a match between the security and the account it is to sum the prices and divide by 2. It the security is new it needs to add a 0 and divide by two. So for every scenario where there is something in one month but not in the other it should simply add by 0 and divide by two. Sounds easier than it is.
Each security is listed by its cusip (identifier), the account that its in and the end of month price for the total account holdings. So for two months there would be three columns of data for each month for a total of 6 columns.
What I have to do is find the average price between the two months.
I usually sort by account and then when both accounts have the same security I just add them and divide by two. The problem is that every month some accounts no longer exist or no longer hold the security. Also some accounts get added and some securities get added. I need a way for excel to look and see if there is a match between the security and the account it is to sum the prices and divide by 2. It the security is new it needs to add a 0 and divide by two. So for every scenario where there is something in one month but not in the other it should simply add by 0 and divide by two. Sounds easier than it is.