130 likes | 144 Views
Explore performance benchmarking options for a 34-year-old MBA manager in a growth fund using peer and market benchmarks. Learn data analysis steps, cleanup techniques, and SQL basics for efficient assessment.
E N D
Data assessment for performance Venky Nagar
The performance data of mutual fund managers Find appropriate performance benchmark for a 34-year old MBA manager in a growth fund. What are the possibilities? Market benchmark Peer benchmark -- what is a peer?
The performance data of mutual fund managers • What are the steps? • Data cleanup and merge: Excel → Access → Excel • Data analysis • Groups: vlookup • Pivot Table • Regression (later)
Data cleanup in Excel • Sort to see missing data, etc. • Do min max to check for errors • Ctrl-shift-arrow to select and move to end of current row or column • Ctrl-arrow to just move to end of current row or column • Ctrl-shift-end to select and move to the end of the data • Ctrl-end to just move to end of data • Shift-arrow to resize selected data
SQL (Structured Query Language) database • Import Excel sheets into Access • Create queries to select only those rows and columns from an Excel sheet that meet your specific criteria • Save the results of queries as sheets • Import them back into Excel
SQL basics Using SQL in a spreadsheet: The idea is to pull out all rows and columns in a spreadsheet that match some criteria and dump the results into a new spreadsheet Select * where criteria Select distinct * where criteria Note: * is shortcut for all columns; you can also choose what columns you want
SQL criteria for dating app Suppose you have a spreadsheet of people: Select distinct * where Sex = ‘M’ and Height >= 6 or MBA = ‘Y’ Select distinct * where Sex = ‘M’ and (Height >= 6 or MBA = ‘Y’) Select distinct * where (Sex = ‘M’ and Height >= 6) or MBA = ‘Y’
Merging the results of two queries • You have two spreadsheets • You want to merge them • The Excel Merge Query tool is very useful • How many different types of merges are there? - Excel handles them all
2016 Earnings and Stock Returns for various firms How many ways to merge?
Peer Groups Performance evaluation by peer groups • Create peer groups in Excel using vlookup • Use Pivot Table to compare peformance of various peer groups
Peer Grouping Tool vlookup(age, agegroup, column2, 1) Age Table (of your brothers) 20 35 49 77 “The age of your brother who was born with or immediately after you” Your age is say: 20, 22, 35, 37, 48, 49, 55, 59, 77, 79, 90, 19 The answer is: 20, 20, 35, 35, 35, 49, 49, 49, 77, 77, 77, NA
Peer Grouping Tool vlookup(age, agegroup, column2, 1) Age groupname Comment 20 Young 20 to 34 years 35 Prime 35 to 48 years 49 Old 49 to 76 years 77 Dead 77 years and above Vlookup will look for a exact match, and failing that will find the number just below Vlookup of 20 will fetch Young Vlookup of 36 will fetch Prime Vlookup of 19 will fetch NA
Pivot Tables • Can easily find average returns for each peer group • Allows for multi-dimensional peer groups • Average returns for peer groups based on not just Young/Prime/Old/Dead but also MBA or no MBA