1 / 13

Data assessment for performance

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?.

almar
Télécharger la présentation

Data assessment for performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data assessment for performance Venky Nagar

  2. 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?

  3. 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)

  4. 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

  5. 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

  6. 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

  7. 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’

  8. 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

  9. 2016 Earnings and Stock Returns for various firms How many ways to merge?

  10. Peer Groups Performance evaluation by peer groups • Create peer groups in Excel using vlookup • Use Pivot Table to compare peformance of various peer groups

  11. 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

  12. 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

  13. 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

More Related