Investment Returns Standardized

The prices of individual investment securities can vary widely and thus a common reporting practice is to standardize or index these values to a baseline value such as 100. For example, Apple’s (AAPL) stock price is currently $188.84 whereas Ford Motor Company’s (F) stock price is currently $11.88. Without any standardization, a chart of the stock prices for these two securities would fail to indicate relative investment performance.

In this blog post we’ll walk through an approach to A) compute the daily return % for a security via Power Query (M) and B) compute an indexed or standardized asset value via DAX.

Starting Point: Daily Closing Prices Per Security

Your source data is a three column table with each row representing the closing price of a given security on a given date such as the following:

Source Table

You can download this data for free from sites such as Yahoo!Finance and it could be stored in a database or other source accessible by Power Query (M), which is basically any source. In this example, the prices for three different securities (tickers) for the past 30 days is stored in Excel with a table name of PriceData.

Step 1: Daily Return % Column

We now need to add a column to the source table which represents the percentage difference of a price relative to the previous day for a given security (e.g. SPY). Specifically, the calculation logic needs to compare the current and prior day’s price for a given security with the prior day meaning the prior trading day, not prior calendar day.

The following M query implements this logic via a sequential index column and conditional logic for referencing a prior row within a Table.AddColumn() function:

 Source = Excel.CurrentWorkbook(){[Name="PriceData"]}[Content],
 Types = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Adj Close", type number}, {"Ticker", type text}}),
 Sort = Table.Sort(Types,{{"Ticker", Order.Ascending}, {"Date", Order.Ascending}}),
 IndexCol = Table.AddIndexColumn(Sort, "Index", 1, 1),
 DailyReturn = 
 Table.AddColumn(IndexCol, "Daily Return %", each
 if [Index] = 1 then null
 else if IndexCol{[Index]-2}[Ticker] <> [Ticker] then null 
 ([Adj Close] / IndexCol{[Index]-2}[Adj Close]) -1 
 , type number),
 ReturnRowsOnly = Table.SelectRows(DailyReturn, each ([#"Daily Return %"] <> null))

The sorting variable (step) precedes the index column such that the previous trading day’s price for a security can be directly referenced relative to the current row. With the Daily Return % column defined, the rows without a daily return % value (the first date for a given security) can be filtered out.

The Power Query (M) can now be loaded to Power BI, Analysis Services Tabular, or Power Pivot for Excel. In this example, the M query is loaded to Power Pivot for Excel:

Loaded Table
M Query loaded to Excel’s Data Model (Power Pivot)

The format for the Daily Return % column is set to Percentage in this example. Note that the sequential index column created to support is hidden from client tools. (As an aside, we now have filtering and sorting capabilities in the Data view of Power BI Desktop per the June 2018 Release)

Step 2: Standardized DAX Measures

We can now develop DAX measures which standardize the prices of the different securities to a baseline value, 100 in this case. Specifically, these calculations will add the value 1 to each Daily Return % value (in the filter context) and compute the product of these values via the PRODUCTX() DAX function. For example, -.08% will become .99155 for SPY on 5/17 and this .99155 will be multiplied by other daily return values.

Finally, date filter logic can be added to these product calculations to support specific date intervals and a cumulative total (ie since the first date). In this implementation, DAX variables have been used for the product calculation to consolidate the logic of the standardized measures:

Index Value Cumulative = 
VAR CumulativeProductCALCULATE(PRODUCTX('PriceData','PriceData'[Daily Return %] + 1), 
   FILTER(ALL(PriceData[Date]),'PriceData'[Date] <= MAX('PriceData'[Date])))
IF(HASONEVALUE(PriceData[Ticker]),CumulativeProduct * 100,BLANK())

Index Value Date Interval =
VAR ProductDateIntervalCALCULATE(PRODUCTX('PriceData','PriceData'[Daily Return %] + 1), 
    FILTER(ALL(PriceData[Date]),'PriceData'[Date] >= MIN('PriceData'[Date])  
    && 'PriceData'[Date] <= MAX('PriceData'[Date])))
IF(HASONEVALUE(PriceData[Ticker]),ProductDateInterval * 100,BLANK())

The only difference between these two measures is the date filter logic. The IF…HASONEVALUE() logic protects against multiple securities in the filter context such as subtotals.

A quick look at Index Value Cumulative:

Standardized Measure: Cumulative

You can now compare and display the securities in an intuitive format. For example, as of 5/22/2018, F had performed best (101.1), XLF was second (100.4), and SPY was third or last at (100.1). Again, this calculation logic includes all the preceding return values up until the given date.

Users will surely want to filter for specific time frames to ask the question: “What if I bought security X on this date and then sold it on that date?” This is where the date interval measure is used:

Index Value Date Interval vs. Index Value Cumulative

In the above pivot table, the dates of 5/22 through 5/25 are selected (four dates). The Cumulative measure therefore computes the standard value with all daily through 5/25 including the days earlier than 5/22. The date interval measure, however, computes the index value only for the dates selected.

A little context: “We do this in Excel…”

I was working with an investment firm recently and naturally an analyst team had extensive Excel-based reports and analyses. The question was “Can we implement this same logic in Power BI?” I assume a common concern for advanced Excel users is how to replicate Excel functions (e.g. SUMPRODUCT(), INDEX()) and existing manual Excel processes with Power BI tools. It’s probably not practical in most environments to migrate all Excel reporting content to Power BI or even Power Pivot for Excel. However, per the example in this blog post, it’s usually a matter of getting familiar with M, DAX and data model fundamentals.

*A broader point for another time is the misconception that DAX is the only language in Power BI/MSBI and thus training on DAX will enable you to write all the calculation logic needed. If you go down this path, either blindly or consciously ignoring Power Query (M) and/or other query languages (T-SQL), perhaps hoping that all required data transformations will be handled by someone else or some other system, in many cases you end up with less scalable, less performant, and less manageable model.

Wrapping Up

  • With the Power Up virtual event session on data modeling completed last week I don’t plan on delivering any further presentations for the rest of the year.
  • I do plan to write a follow up blog post to Data Model Anti-Patterns after next week.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s