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 […]
Three Takeaways
Does it ever seem awkward to run Visual Basic subroutines within Microsoft Excel? If you have a plain unvarnished subroutine, you must click through to the Developer tab, and then point and click to find the macro of interest. “There are more convenient ways to access the macros you build,” said Andrew Nikolishyn of Vesta Solutions. He was the instructor of a day-long seminar on “Visual Basic for Financial Professionals,” held on location at the CFA Society of Toronto on May 1, 2015. In Part 1 of this posting, I described how participants could access the Developer tab to create […]
Hum a Few Bars and I’ll Fake It
How far can you go at reducing the tedium in your spreadsheet usage? On May 1, 2015, nearly twenty people attended a day-long seminar “Visual Basic for Financial Professionals,” held on location at the CFA Society of Toronto. The seminar was conducted by Andrew Nikolishyn, CFA, of Vesta Solutions. I often use Microsoft Excel as a scratch pad and a tally sheet, but lately I’ve had to do connected tasks on a regular basis, involving recent financial data. I prepare a graph from the data, and I also run a simple present value calculation, to compare with the previous month’s […]
Better Living Through Topology
“It gets downright annoying when I’m just trying to check my e-mail but I get prompted to answer three security questions,” said Alexis Johnson, voicing a concern shared by many in the audience when fraud detection is so overblown it obstructs ordinary use of software. Johnson, Director of Technical Sales at the big data analytics firm Ayasdi, was the second of two presenters at a webinar on the topic “The Fraud Arms Race” sponsored by the Global Association of Risk Professionals on April 21, 2015. When it comes to detecting fraud in big data, “data complexity, not volume, is the […]
Tailoring Risk Model to Investment Strategy
Due to the growing complexity of measuring financial risk, “risk has become a patchwork” of different models, said Phil Jacob, Senior Director at Axioma Risk Research. He was the sole presenter in a webinar about tailoring the right risk model to your investment strategy held on March 4, 2015, and sponsored by the Global Association of Risk Professionals (GARP). Jacob identified four inherent challenges. “There are operational issues stemming from existing rigid approaches,” leading to “difficulty in aggregating risk.” There is a lack of consistency in modeling portfolios, which can run the gamut from very simple proxies all the way […]