Data processing

To process the data we used Microsoft Excel and Visual Basic programming. The data sample is quite comprehensive and it took some time to find an efficient way to do the calculations. We ended up with a total of 550 and 3008 different portfolios for non-overlapping holding periods and overlapping holding periods respectively. Excel macros eased the process of creating the portfolios.

Most of the academic papers within the field only give a short description of the data treatment, but do not offer any insights on the calculations and processing of data. We want to give a short description with illustrations of our method for both non-overlapping holding periods and overlapping holding periods. This can be useful for recreation or future studies within empirical finance.

5.5.1 Non-overlapping holding periods

Calculating returns from non-overlapping holding periods was the first part of the data processing we worked on in excel. Using a 6x6 strategy as an example we will explain step by step our data processing work. The first step was to sum up the returns during the first six months of our sample and label it “P1 Formation”. The sum of the next six months would be labeled “P1 Holding”. “P1 Holding” is equal to “P2 Formation”, “P2 Holding” is equal to “P3 Formation”, etc. We ended up with a total of 17 holding periods for this strategy. The next step is to rank all the stocks in each formation period according to the top 20 percent and bottom 20 percent by using the conditional formatting function in Excel. The third step was to manually color the cells in each holding period red if the return in the formation period was among the bottom 20 percent, and color the cell green

if the return was among the top 20 percent. This is illustrated in Figure 5-4 below. From our experience the coloring part takes quite some time because we had 17 holding periods, with 18 winner stocks and 18 loser stocks in each holding period. This means that we needed to manually color 17x36 = 612 cells. To the right in Figure 5-4 we can see the returns from the winner portfolio and loser portfolio in each holding period. To calculate these we made a VBA formula that calculated the sum of the cells with identical colors. The VBA formula can be found in Appendix II.

The equally weighted average was then calculated by dividing the sum by the number of stocks in the portfolio. This was done for both the winner and loser portfolios. The reason why we needed to manually color the cells was because the VBA formula we made could not register the “lighter colors” produced by conditional formation function in Excel. In the end we calculated the average return per month for the winner, loser and zero-cost portfolio by dividing the return results by the length of the holding period, which in this case was 6 months.

Figure 5-4 Non-Overlapping Method

5.5.2 Overlapping Holding Periods

At the next stage of our data processing work we would calculate momentum returns using overlapping holding periods. As illustrated in Figure 5-3, a new portfolio is created each month, which means the number of formation and holding periods increase drastically. The 6x6-strategy would leave us with 97x36 = 3492 cells to color using the same method as for non-overlapping method. As explained above, manually coloring the cells was a time consuming process. We therefore started to consider new ways of handling the process more efficiently. In our original study we had 90 stocks and nine years of returns. However, to illustrate our method we will use a simplified sample with only six stocks and half a year with returns. This method is illustrated by the 3x3 strategy with a formation and holding period of three months.

Figure 5-5 Calculation of Formation Period

The first step is to calculate the returns during the formation period, which is the accumulated return of the three months before initiation of the portfolio. The SUM-formula in Excel is equal to equation 8 in section 5.4. The accumulated returns for the holding period are calculated the same way, but for the subsequent period. The holding period return for the first portfolio would be the summation of April, May and June. The accumulated returns are gathered in one matrix as illustrated in Figure 5-5 above.

Figure 5-6 Ranking of Stock Performances

Following the summation we made an equally large matrix for the ranking of the stocks. As the formula describes each stock is ranked compared to the other stocks in the same formation period.

This is done for all subsequent formation periods as shown in Figure 5-6. The column “Portfolio”

separates the different formation periods and show which periods that are connected across tables.

For example, portfolio 2 in the “Ranking” table shows the stock ranking in the second formation period.

Figure 5-7 Creating the Portfolios

After the ranking we add together the portfolios based on their performance. To do this we use the

“Ranking” matrix and the holding returns in the “Holding Period” matrix. As mentioned the holding returns are calculated as the formation returns in Figure 5-5. Portfolio 1 in “Holding Period” shows the 3-month return after the first formation period. Portfolio 2 in “Holding Period” shows the 3- month return after the second formation period, and so on.

To construct the winner portfolio we use an IF-function that we created using VBA. For this example, the winner and loser portfolio will consist of the two best and worst performing stocks, and the VBA-function is sorting them out. In Figure 5-7 the return from the holding period will show in the “Winner Portfolio” table if the stock is ranked 1 or 2, and if not, the cell is left blank.

The loser portfolio follows the same procedure in a own table with a similar VBA function that shows the holding period if the stock is ranked 5 or 6, if not, the cell is left intentionally blank. For

our base study we included the 20 percent best and worst performing stocks in the zero-portfolio and the VBA function we used can be found in Appendix II.

Figure 5-8 Strategy Results

The return of the portfolio is simply the average of the row that only shows the returns of the stocks included in the portfolio. These average returns are summarized in Figure 5-8 and the zero-cost portfolio is calculated for each portfolio. We then take the average of all the portfolios, giving us the final result from the 3x3 strategy. This methodology was applied for all 16 momentum strategies.