I am an investment advisor and each quarter I need to check client accounts and make sure that the percentage of an investment account in a particular investment is within permitted bounds.
? Data comes in a worksheet in one format.? I need pulled across to a second worksheet.? I cannot use Excel formula to pull data (e.g. vlookup function) as depending on the data in the source different courses of action will be needed.
specific requirements can be provided.? I would imagine visual basic would be needed.
?
## Deliverables
I am an investment advisor and each quarter I need to check client accounts and make sure that the percentage of an investment account in a particular investment is within permitted bounds.
Data comes in a worksheet in one format. I need pulled across to a second worksheet. I cannot use Excel formula to pull data (e.g. vlookup function) as depending on the data in the source different courses of action will be needed.
I receive data for each account in an Excel worksheet (Source [login to view URL]) with the columns: Account Number, account name, ticker symbol or CUSIP (This is a unique five to eight character identifier of an investment), security name and current value. E.g.
Account Number Account name? ? ? ? ? ticker security name? ? ? ? ? ? ? ? ? Value
71667011? ? ? ? ? ? ? ? ? Joe Peterson IRA AVVXX Aim Constellation? ? ? ? ? $13,000
I have a second worksheet (Master Client Asset Allocation [login to view URL]) which needs to pull data across form the first worksheet. The second worksheet contains dozens of tables. One table per account number. Each table looks like this:
71667011 Joe Peterson IRA
AVVXX AIM Constellation $13,000
MCMXX Putnam Growth $24,000
8051608 Bank of America 6 month CD $12,333.32
etc.
I need a program that I can activate from Master Client Asset Allocation [login to view URL] which will open Source [login to view URL] (which will be in the same directory) and then will do the following:
1. Each security value in Master worksheet is set to zero.
2. Use the ticker symbol on the Master worksheet to index into Source Data and pull across all current values.
3. If there is a ticker symbol on Source [login to view URL] that does not exist on Master Client Asset Allocation [login to view URL], the program inserts a row in the account's table after the first line of existing data by copying the first line of existing data, and then overwriting the ticker, security name and value. This new position is flagged by entering an asterisk in column one of the Master worksheet.
4. Then an error checking mechanism runs by totalling the amount in each account in Source [login to view URL] and compares it to the total amount in each account in Master worksheet. If there is an error that should be flagged in column two by a “**??
5. A second error checking routine runs which checks to see if there is an account number in the Source Data worksheet that is not on the master worksheet. If so, the missing account number and name should be written to the second tab of the Master worksheet.