Data assessment for performance Venky Nagar The 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?

David Hume’s First Inquiry (Human Understanding) 1748 ● Correlation vs causality ● Hypothesis testing When we run over libraries, persuaded of these principles, what havoc must we make? If we take in our hand any volume; of divinity or school metaphysics, for instance; let us ask, Does it contain any abstract reasoning concerning quantity or number? No. Does it contain any experimental reasoning concerning matter of fact and existence? No. Commit it then to the flames: for it can contain nothing but sophistry and illusion.

History of Unsupervised Learning in AI ● ● ● ● ● USA spying on USSR and needed translations of manuals CS Phd student Robert Mercer and IBM antitrust in Canada Depositions in English and French Robert Mercer and advanced Pivot Tables and groupings Robert Mercer and government Robert Mercer and money woes and Renaissance Robert Mercer and Siri Robert Mercer and Breitbart and Trump Robert Mercer and Association of Computational Linguistics ● AT&T mega leader in AI but disbanded its group just as AI was getting hot…

The performance data of mutual fund managers 1. What are the steps? 2. Data cleanup and merge: Excel → Access → Excel 3. Data analysis a. Groups: vlookup b. Pivot Table c. Regression (later)

Data cleanup in Excel 1. Sort to see missing data, etc. 2. Do min max to check for errors a. Ctrl-shift-arrow to select and move to end of current row or column a. Ctrl-arrow to just move to end of current row or column b. Ctrl-shift-end to select and move to the end of the data a. Ctrl-end to just move to end of data c. Shift-arrow to resize selected data

SQL (Structured Query Language) database 1. Import Excel sheets into Access 2. Create queries to select only those rows and columns from an Excel sheet that meet your specific criteria 3. Save the results of queries as sheets 4. 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: Name Sex Height MBA Salary Xie F 6 N $300 K Joe M 5 Y $290 K … 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 1. 2. 3. 4. 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? Company Earnings Growth Company Stock Returns Amazon 5% Amazon 10% Apple 6% Apple 4% IBM 9% P&G -5% IBM 8% GE IBM -3% 4%

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

Peer Grouping Tool vlookup(age, agegroup, column 2, 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, 35, 35, 49, 49, 77, 77, NA

Peer Grouping Tool vlookup(age, agegroup, column 2, 1) Age groupname Comment 20 35 49 77 Young Prime Old Dead 20 to 34 years 35 to 48 years 49 to 76 years 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 1. Can easily find average returns for each peer group 2. Allows for multi-dimensional peer groups 3. Average returns for peer groups based on not just Young/Prime/Old/Dead but also MBA or no MBA