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 […]
The Fraud Arms Race: Neck and Neck
“It’s the fraud axiom: all the things we do to make things easier for the consumer also make things easier for the crooks,” said Randall Casciello, Advanced Analytics Senior Manager at the management consulting firm Accenture. He was the first 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. Fraud from payment cards costs the financial services industry around $12 billion annually, and is increasing at an alarming rate, about 15 percent annually, according to the Nilson Report. Casciello said fraud spans a wide […]
Swaps, Before & After
“Historically, there were few, if any, regulatory requirements on swaps … it was effectively an unsecured loan,” said James Schwartz, Of Counsel at Morrison & Foerster. He was the fourth and final presenter at the Derivatives Regulatory Update webinar held on March 31, 2015, sponsored by the Global Association of Risk Professionals. Prior to the Dodd-Frank Act, swaps dealers were self-regulated through the trade association International Swaps and Derivatives Association (ISDA). “It was typical for the two parties to accept a certain amount of uncollateralized exposure to each other in the form of a threshold that varied according to their […]