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 […]
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 […]
Fama-French Model 1. Three is Now Four
Does the Fama-French three-factor model adequately capture all information available in describing stock returns? According to Marlena Lee, PhD, VP of Dimensional Fund Advisors, the three-factor model is lacking one or two important components. Lee visited the Toronto offices of the CFA Society Toronto on the afternoon of June 19, 2014, to speak to over twenty financial experts about the evolution of asset pricing. Lee was a funny and forthcoming lecturer. After her flight from the States up to Toronto, she said the suspicious Canada Border Services officer asked: “This CFA Society… what does ‘CFA’ stand for?” She momentarily blanked: […]
SM 4. A Way Of Sparking Conversations
Over the past six years, Jaime Widder, Sales Manager at Bloomberg LP, has seen an evolution in his clients with respect to the data they seek from social media. “At first, they just wanted to look at social media information on clients. Now, portfolio managers want to know how to list securities and track social velocity.” Widder was the fourth panellist (of four) to address the topic “The Impact of Social Media on the Investment Process” at a CFA Toronto Society gathering held in the gallery of the TMX Broadcast Centre on the evening of May 21, 2014. For clients who […]
SM 3. Keep Pace with Customers
“I work for a company with positive social capital,” said Kavita Joshi, Associate Vice President, Social Media at TD Bank Group. She was the third of four panellists at a CFA Toronto Society gathering titled “The Impact of Social Media on the Investment Process” held in the gallery of the TMX Broadcast Centre on the evening of May 21, 2014. “Social media means we are trying to keep pace, not with our competitors, but with our customers,” Joshi said. “We’ve realized there’s a real shift in terms of responsiveness.” The entry point to social media is around customer service, advised […]