Conference Call Tones. Part 2
Click here to visit Part 1. Interview with S. McKay Price, continued. Q: In the introduction to your paper on textual analysis of conference call tones, you describe a 2012 conference call in which David Einhorn grilled the management team of Herbalife, thereby causing the shares to fall 20 percent in price. Did you run the transcript of this conference call through your call tone algorithm, and if so, was it the most negative sample in the set? 2012 was not in our sample period so we did not specifically create tone measures for that Herbalife call. Although I suppose […]
Old Dog, New Tricks. List of Tricks
The following are a dozen helpful things I learned at a day-long seminar “The Power of Excel – Part 2,” held on location at the offices of the CFA Society of Toronto on June 10, 2015. The seminar was conducted by Jon Zelman of The Marquee Group. 1. Resist the Mouse 2. Best shortcut of the day 3. ALT-ernative Existence 4. If you filter, use SUBTOTAL, not SUM 5. To count the number of visible rows 6. Searching with multiple conditions 7. D is for Database function 8. “Exact” string matching versus “includes” 9. Multiple conditions for SUMIF 10. Weed out bad parameters early with Data Validation 11. Match and Index functions complement […]
Old Dog, New Tricks. 11 & 12
11. Match and Index functions complement each other “The INDEX function is one of the most powerful but also one of the most complex functions in Excel,” Zelman said. Both INDEX and MATCH are Lookup functions in Excel, so whatever comfort you have with VLOOKUP or HLOOKUP will be useful here. Big picture: you use MATCH to find the coordinates, and you use INDEX to apply the coordinates to find a value. The Match function returns the relative position of an item within a column of data (or a row of data). The syntax is MATCH(lookup_value, Lookup_vector, Match_type). Note I […]
Old Dog, New Tricks. 9 & 10
9. Multiple conditions for SUMIF Let’s say a table has a dollar column you want to add up, but only for entries that have “Industry” = “Finance” whose “Lead Underwriter” is “Morgan Stanley”. You could set up Filter and then use SUBTOTAL, as described above. Another way to get the answer is using a type of SUMIF … the type with multiple conditions. To give multiple conditions to a function such as SUMIF or COUNTIF, use the functions SUMIFS or COUNTIFS instead. The general syntax is SUMIFS(dollar column to be summed, column of name1s, name1 criterion, column of name2s, name2 […]
Old Dog, New Tricks. 7 & 8
7. D is for Database function “Excel’s Database functions are some of the most powerful and underutilized functions in Excel,” Zelman said. Those formulas starting with “D” such as DAVERAGE, DCOUNTA, DVAR, and so on, are all set up to work on a database, which is typically shown as a flat table of numbers and text (rows and columns). The Database functions all use the same syntax: DCOUNTA(Database, field, criteria). He specified four steps for setting up a Database function. Ensure the raw data has column headings. Add a few blank rows (“the sandbox”) at the top of the raw […]
Old Dog, New Tricks. 5 & 6
5. To count the number of visible rows Let’s say you are given an Excel table listing all Winter Olympics events, medals, athletes, and countries. The question might arise: “How many gold medals were won by Sweden?” On the Data ribbon, select Filter. This will put drop-down lists for each column. Make your selection, by setting two filters in the “Medal”= “gold” and “Country” = “Sweden” columns. (Note: to do this only using the keyboard, use Arrow down to move and Tab to select.) In the bottom row, type in “=SUBTOTAL(3,A11:A268)”. In this case, the first argument 3 refers to the […]
Old Dog, New Tricks. 3 & 4
3. ALT-ernative Existence In Excel, the problem with keyboard shortcuts is that, over time, I forget what sequence summons up a spreadsheet command such as, say, Filter. Zelman showed that Excel makes it easy to refresh my memory on the desired sequence. It all boils down to judicious use of the Alt key. With your Excel file open, click the Alt button. (The sample file is called “chem_midterm”–but don’t let that turn you off!) The letters displayed show how to summon the desired tab or ribbon, as can be seen here: To access the Data functions, click Alt + A […]
Old Dog, New Tricks. 1 & 2
When I use Microsoft Excel, am I making the most of the spreadsheet functions available? What are the most helpful shortcut keys in navigation and formula input? I needed a review course to rejuvenate my old tricks and learn some new ones. On June 10, 2015, about a dozen people attended a day-long seminar “The Power of Excel – Part 2,” held on location at the offices of the CFA Society of Toronto. The seminar was conducted by Jon Zelman of The Marquee Group. Zelman’s years in investment banking and financial analysis have given him a deep knowledge of Excel, […]
Four Lessons from Stress Testing Exercise
“It very quickly became apparent that this was not a one- or two-month exercise,” said Charyn Faenza, Vice President, Manager of Corporate Business Intelligence Systems at First National Bank, the largest subsidiary of the largest subsidiary of FNB Corporation. She was the second of two presenters at the May 19, 2015, webinar on Stress Testing Modeling sponsored by the Global Association of Risk Professionals. Faenza was referring to her bank’s experience as an example of a “DFAST 10-50” bank that is required to conduct an annual stress test. She drew four important lessons from the exercise. Good Modeling Requires Good […]
“Not Only The What But The How”
When it comes to financial data for stress testing, there’s a good news-bad news aspect. The good news may be that a bank did not suffer severe financial stress but the bad news is that it will be harder for the bank to model “bad events” if it does not have such data. And banks “will get written up if [the regulators] don’t believe their bad events,” said Tara Heusé Skinner, Manager at SAS Risk Research & Quantitative Solutions, and co-author of The Bank Executive’s Guide to Enterprise Risk Management. She was the first presenter of two at the May […]