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, […]
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 […]
Hedge Fund Fallacy
“If all the money that’s ever been invested in hedge funds had been put in treasury bills instead, the results would have been twice as good,” said Simon Lack, founder of SL Advisors, LLC, and author of The Hedge Fund Mirage: The Illusion of Big Money and Why It’s Too Good to Be True. This statement was his opening volley in a webinar titled “The Hedge Fund Fallacy” on May 5, 2014, sponsored by the CFA Institute. Lack began with a “typical portrayal” of hedge fund returns: a 6 percent return over the period 1998 to 2013. Over the same […]
Investing in Emotional Assets
When Morty discovered my vacation plans this year included Cremona, that put a bee in his bonnet. Did I know it was the home of the famous Stradivarius workshop? Was I going there to check out violins? Yes and no, I said. My preference was to cycle along the Po valley and sip espresso near the Zodiac Tower. “Maybe you should think twice about your visit,” Morty said. “I happen to have it on good authority that beautiful collectibles can be a solid component of your portfolio.” “Please Morty, don’t make wild-eyed guesses about other people’s money,” I said. Especially […]
Interview with William Bernstein: The Paradox of Wealth
I could hear the laughter from down the hall. “Cheeseburger,” cried Morty. Cheeseburger? I saw he was holding a back issue of the Financial Analysts Journal. (It’s no secret that we fall behind on our reading here during the busy months.) “You’ve gotta interview this guy,” declared Morty. “Anyone who can work the word ‘cheeseburger’ into the pages of this esteemed journal… well… he likely has something interesting to say.” William Bernstein did indeed have some very interesting things to say. The interview below is the vegan-friendly edition, though; if you want to see ‘cheeseburger’ in print you’ll have to […]
Fama-French 2. Three is Now Five?
Fama and French, originators of the three-factor model for asset pricing, are working to understand the fourth factor –and a fifth factor, too, said Marlena Lee, PhD, VP of Dimensional Fund Advisors. She should know; she has worked closely with Nobel laureate Gene Fama and was his former teaching assistant at the University of Chicago Booth School of Business. Lee spoke at the CFA Society Toronto on June 19, 2014, about the evolution of asset pricing. Part 1 summarizes her comments on the dimension of profitability. Could there be another component? As early as 1993 Jegadeesh and Titman had proposed […]