Do you work in sales & trading? Have you ever had the head of your desk approach you and say, "Could you just run me a quick report to find out how many clients have dealt with us during 2009?"... Have you said "Yes" but thought to yourself, "Do I look like a spreadsheet jockey? Why is he getting me to do that? And more importantly; How on earth do I do that...?"
First, the excel tip, then the career advice...
First, get a data dump of all trades dealt with your desk during the calendar year 2009. (You might have an internal trade reporting system that can give you this, or if you have an effective business management team, they should be able to give you the data.) There will be a column here called "counterparty name" or something similar. In isolation, this column can be viewed as a list of 39,564 names with duplicates. For example, if Counterparty A traded 651 times with your desk throughout 2009, then their name will be listed 651 times. Your task is to consolidate that list into a list of unique names, so that Counterparty A's name appears only once, alongside single entries of all other names.
>> Copy and paste the "Counterparty" column to a fresh part of your spreadsheet (a new tab or to the right of the original table).
>> Fill the column to the right of the list with the number 1
>> Select the cell to the top right of the list (in the screenshot, cell J1)
>> Select Data > Consolidate... from the Menu Bar (or use keyboard shortcut Alt > d > n)
>> Highlight the list of counterparties and the column to the right of that (filled with "1s")
>> Select "Sum" from the drop down list and click "Left Column" from the check boxes
>> Click the OK button to generate a list of unique entries. Excel will Sum the number "1s" beside each name and tell you how many of each entry there were in the original list too, i.e. how many trades each counterparty dealt with your desk
>> Use the Count function to count how many clients had dealt with your desk over 2009
Now, the career advice... although you want to show your boss that you are capable of doing more intellectually taxing tasks, the way to do this is not to shun the more mundane tasks he or she may throw at you. They know that you are smart, that is why they hired you in the first place. Do the simple things well and it will send them the message that you are not only smart but have the right attitude too.
First, the excel tip, then the career advice...
First, get a data dump of all trades dealt with your desk during the calendar year 2009. (You might have an internal trade reporting system that can give you this, or if you have an effective business management team, they should be able to give you the data.) There will be a column here called "counterparty name" or something similar. In isolation, this column can be viewed as a list of 39,564 names with duplicates. For example, if Counterparty A traded 651 times with your desk throughout 2009, then their name will be listed 651 times. Your task is to consolidate that list into a list of unique names, so that Counterparty A's name appears only once, alongside single entries of all other names.
>> Copy and paste the "Counterparty" column to a fresh part of your spreadsheet (a new tab or to the right of the original table).
>> Fill the column to the right of the list with the number 1
>> Select the cell to the top right of the list (in the screenshot, cell J1)
>> Select Data > Consolidate... from the Menu Bar (or use keyboard shortcut Alt > d > n)
>> Highlight the list of counterparties and the column to the right of that (filled with "1s")>> Select "Sum" from the drop down list and click "Left Column" from the check boxes
>> Click the OK button to generate a list of unique entries. Excel will Sum the number "1s" beside each name and tell you how many of each entry there were in the original list too, i.e. how many trades each counterparty dealt with your desk >> Use the Count function to count how many clients had dealt with your desk over 2009Now, the career advice... although you want to show your boss that you are capable of doing more intellectually taxing tasks, the way to do this is not to shun the more mundane tasks he or she may throw at you. They know that you are smart, that is why they hired you in the first place. Do the simple things well and it will send them the message that you are not only smart but have the right attitude too.
Posts
No comments:
Post a Comment