Horizontal Sorting - the HSORT function
You would like to pursue a sector rotation strategy, and need to rank all of the assets according to a particular criterion. First let’s create a dataset with all of the assets being ranked. There may be hundreds of such assets, and we do not want to enter them all manually, so let’s set up a loop that pulls all of the datasets out of a list, and stacks them side-by-side according to dates they have in common.
Here’s the list of Dow Jones European stock indices, identified as “Euro_industry_sectors”
Then here’s the loop construction:
** N.B. The text of all commands is located at the end of this document. That way you can simply copy them over to your own command set, should you wish to duplicate or modify the research. A line-by-line explanation of the operation of the loop is also at the end of this document. Graphics Memory Warning: The operations here perform lots of calculations and generate enormous output. Should you do this yourself, do not have all of the output open at the same time. Doing so is likely to exhaust your Windows Graphics Memory, which is typically much smaller than regular memory.
Here’s what the result looks like.
Should you enter “dj_euro_stoxx_sectors first 1”, you will learn that the first entry that is common to all of the sectors is 19911231, or December 31, 1991. Should you enter “length dj_euro_stoxx_sectors” you will see that the dataset has 3441 rows (daily dates). This is obviously enough data for a valid test.
Next we need to produce the measurement by which we are going to rank the sectors. Separately one of our principals has written articles recommending the moving slope rate of change (MSROC) as an excellent momentum indicator for such an exercise. MSROC has the advantages of being timely, smooth and price-independent. Let us then calculate a 63-day MSROC and a 21-day MSROC for each sector, and average those two MSROCs. Note that there are typically 21 trading days in a month and 63 trading days in a quarter. Thus we are looking to rank the sectors according to the average of monthly and quarterly momentum.
Here’s the output:
** N.B. To keep the column labels consistent, we have supplied text at the end for applying column labels.
Before we go about picking our strongest sector, we should consider the possibility that all equities will be below our investment threshold. That is, is it possible that we would not want to be in equities at all, perhaps because they were all declining in value? Here we are going to recommend that if the yield on cash is greater than the yield on equities, you move to cash. But let’s go one step further and place cash in higher esteem by comparing its 21-day MSROC to the combined longer-term MSROC of the equity sectors. The yield on cash will be the 3-month Euribor rate (substituting the 3-month German rate before the existence of the Euro).
Above is shown the dataset “eurocashrate”, which is expressed as a yield. What we need is that dataset expressed as an accumulating asset – as though it were a money market fund whose value compounded daily. It can then be compared to our sectors, which are also expressed as accumulating assets. Here’s what to enter:
The top line merely calculates the daily value at which the eurocashrate compounds (on the basis of 252 trading days per year). The second line then compounds them over the full length of the data. It is the algebra of compounding interest rates. The third line calculates the 21-day MSROC of that accruing value, and the last line glues our cash MSROC onto our dataset with the MSROCs of the stock sectors, creating a new dataset and giving it a much shorter name.
Now that we have MSROCs for each asset (the stock sectors and cash), we need to see how they rank. If you request 'up' hsort desmc, you will get a dataset with the data rearranged in the 19 columns from lowest to highest. The command 'down' hsort desmc will sort them from highest to lowest (as you go from left to right).If you have forgotten how many columns you have, just request width desmc.
Seeing the rankings are unnecessary; what we really need is to identify the MSROC value of the highest ranked asset. The statement solves our needs.
Again, the text ('up' hsort desmc) sorts the values in each row and rearranges them in columns with the lowest value in column 1 and the highest value in the last column. If there are 19 columns (as there are in this example), the winning value will be in the 19th column of this dataset. After we have sorted the data such that the highest value is in column 19, we have then requested only column 19. The output will be a 1-column dataset with that highest MSROC value:
Note, if you did not know the number of columns, you could request:
Next, you just need to know which of our 19 columns of the dataset desmc has that value:
This statement identifies the condition when for each day the value of any column of the dataset desmc equals the value of highest_column_value. When that condition exists, the resulting dataset will have a value of 1 in that column, with values of 0 in all of the other columns. In the part of the dataset illustrated below, column 6 is ranked first, but then the winner switches to column 2.
Okay, we now know the sectors in which we should be invested, and when (more on this soon). We next need to calculate what we will earn from those investments. To calculate the daily return of each stock sector asset:
That’s only the equity sectors; don’t forget the cash returns:
Now we want to make this as realistic a test as possible, so we have to account for a lag of 1-day between when we learn which sector is highest ranked, and when we can acquire it.
By multiplying, we produce returns of zero in 18 of our 19 columns, with only a return showing in the winning column. But we still have 19 columns, and need only one.
hsum is the horizontal sum function, which adds all of the columns horizontally, producing one column with their sum.
This gives you daily returns. What you next need to do is to compound those:
The function cumprod takes the cumulative product of each row multiplied by all those going before it.
This worked great in a generally rising market. Unfortunately the only asset it could employ when the overall market was in decline was cash. That in and of itself illustrates the advantages of long/short programs over long-only programs.
What we have shown here is a fairly simple sector rotation study. A few basic modifications will vastly improve the results. For example, the moving slope rate of change was chosen because it has the good characteristics of being both smooth and timely. However, smoothing the raw data prior to performing our moving slope calculations, and additional smoothing after (but still prior to sorting) generated results an order of magnitude greater.
-------------------------------
reset
:for #1 :in Euro_industry_sectors
:if (count 1) = 1
dummy gets #1 col 1
:else
dummy gets (dummy common #1 col 1)
:endif
:endfor
dj_euro_stoxx_sectors showgets dummy
-------------------------------
msroc63: 63 msroc dj_euro_stoxx_sectors
msroc21: 21 msroc dj_euro_stoxx_sectors
dj_euro_stoxx_msroc_comb showgets (msroc63 + msroc21)/2
-------------------------------
Column labeling:
'sx3p,sx4p,sx6p,sx7p,sx8p,sxap,sxdp,sxep,sxfp,sxip,sxkp,sxmp,sxnp,sxop,sxpp,sxqp,sxrp,sxtp' setlabels dj_euro_stoxx_msroc_comb
-------------------------------
dailycashreturn: (1+(eurocashrate/100))^(1/252)
accrue: 100* cumprod dailycashreturn
accrual21msroc: 21 msroc accrue
desmc: dj_euro_stoxx_msroc_comb common accrual21msroc
-------------------------------
highest_column_value: (hsort desmc) col 19
highest_column_value: (hsort desmc) col (width desmc)
sector_winner: desmc = highest_column_value
-------------------------------
equity_gains: dj_euro_stoxx_sectors / (dj_euro_stoxx_sectors back 1)
allgains: equity_gains common dailycashreturn
-------------------------------
combined_gains: (allgains ahead 1) * sector_winner
daily_switch_returns: hsum combined_gains
compound_switch_returns: cumprod daily_switch_returns
-------------------------------
Explanation of the loop:
Line 1: reset any temporary datasets
Line 2: Starts the first loop (#1), calling up the list named “Euro_industry_sectors”. The loop then performs computation in the lines between :for and :endfor sequentially on all dataset in the list Euro_industry_sectors.
Line 3: Line 3 involves the loop function count. It tells you the number of the current value of #1 in the list. That is, #1 represents each name in a list successively. There might be 20 or 200 names in that list. If #1 currently holds the name of the third dataset in the list, then “count 1” would be 3. If #1 currently holds the name of the ninety-eighth dataset in the list, then “count 1” would be 98. If you were also using the variable #2, you would access its count by “count 2”. Thus line 3 is only true when #1 contains the name of the first item in the list.
Line 4: then create a new dataset called “dummy”, and dummy will consist of Column 1 of the first item in your list.
Line 5: If your count in that list is not 1,
Line 6: then take column 1 of the next item in your list and put it together with dummy and rename the new dataset “dummy”.
Line 7: Ends the “if” condition.
Line 8: Ends the loop when all items in the list are considered.
Line 9: Creates (and saves) a new dataset called “dj_euro_stoxx_sectors” from dummy, and displays it.
------------------------------------------------------------------------------