Winds of Change
“Will the greater intensity of climate change expected in Canada produce a greater impact on Canadian financial institutions, and firms that hold a greater proportion of exposure to Canada in its portfolio?” This was the question posed by Robert Thomas, risk consultant to the non-profit organization Ontario Conservation Now as part of a round table held at the Toronto Public Library on April 18, 2019. A report[1] tabled earlier this month by Environment Canada says that climate change will have a greater effect on Canada than on most countries. The report, Canada’s Changing Climate Report 2019 (CCCR2019), looked at observed […]
Robo-Advisors
What does the client want to see on the landing page of an automated wealth management website? Four panellists at the digital wealth management (a.k.a. robo-advisor) session of the Financial Technology conference held on June 17, 2016, had theories on how to connect with clients. The session was part of a one-day conference organized by the CFA Society Toronto and was held at the Toronto Board of Trade. Three of the panellists showed screen shots from their companies’ websites and spoke about underlying philosophies on client usage; the fourth panellist works for a company that provides “back end”, namely, the […]
“Deal With It”
When it comes to technology advances in the fixed income markets in Canada, “peer-to-peer still needs some work,” said Robert Pemberton, Head of Fixed Income at TD Asset Management. His company’s client base “runs the gamut from retail right through to large investors.” Approximately two and a half million are mutual fund clients. He was the third of three panellists at a September 18, 2015, luncheon sponsored by the CFA Society Toronto, and held at the Toronto Board of Trade. Participants were commenting on regulatory changes in the fixed income markets announced the day before. Canadian Securities Administrators (CSA) will […]
“A Step in the Right Direction”
“The new regulation is a solid step in the right direction,” said Steve Thom, Managing Director at RBC Capital Markets, referring to the September 17 announcement by Canadian Securities Administrators (CSA) on a new reporting system. “The new regulation will increase price transparency, which is important for investors to be able to make more informed decisions. This is a good thing,” said Thom, “but the big thing is size.” He was the second of three panellists at a luncheon, sponsored by the CFA Society Toronto on September 18, 2015, to discuss changes in the fixed income markets. However, “full size transparency would […]
Fairness in Fixed-Income Markets
What are the essential building blocks of market integrity? Information, and fair access to that information. Transparency, but not onerous transparency. “There was a lack of fixed-income data, and large investors had better access to data,” said Ruxandra Smith, Senior Accountant at the Ontario Securities Commission (OSC), the first of three speakers at a luncheon sponsored by the CFA Society Toronto on changes in the fixed income markets in Canada. The event was held at the Toronto Board of Trade on September 18, 2015. Smith was referring to hot-button issues identified in the April 2015 report on the Canadian fixed […]
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 […]