Statistical Functions in Excel I

In the first post of Statistical Functions in Excel, we will cover a part of the functions that can be used for statistical analysis available in Excel. While there are several functions with a variety of functionality, we will focus on Statistical functions only in this post. It is assumed that the reader will know basics of Statistics and Like before, we will explore these functions in Excel 2016.


Let's take a quick look at some of these functions that are available OOTB on the Status Bar.

If we enter 1 to 4 in cells as shown above and select them, then, we see the Average, Count and Sum shown on the Status Bar on the right bottom of the window. We can customize the measures shown in the Status Bar by right clicking anywhere on the Status Bar and, then, clicking Numerical Count, Minimum and Maximum from the Menu Items shown. This is shown below:

If we wish to add any more functions, then, we can add the functions in the Status Bar using VBA. But, that is a story for another day. Continuing with the above Menu Items figure, we see above that Average, Count, and Sum are checked. So, we see them in the Status Bar once we select cells containing numbers. Once we click Numerical Count, Minimum and Maximum, we see a tick against these measures as shown below:

To confirm that these measures appear on the Status Bar, we can select cells as shown below and see that the corresponding measures are shown:

Count gives the count of all cells selected while Numerical Count gives the count of all cells containing numbers only. Min and Max give the minimum and maximum of the selected numbers. Now, that we have seen the OOTB functionality, let us quickly see the Statistical functions in alphabetical order:

AVEDEV: Calculates the average of the absolute deviations of data points from their mean. Mathematically, it can be written as shown below where x is a It can take numbers in the form of arguments, but not exceeding 255 in count, like, AVEDEV(1,2,3,4) that will return 1. If more than 255 arguments are passed, then, we get below error:

A work around is to use enter these values in cells and use cell range like, AVEDEV(A1:A2000). This is applicable to many of the statistical functions that we shall see shortly.

AVERAGE: Calculates the average. We can select cells and, then, insert numbers as shown below:



The result of the function is 2.5 and that compares well with the value in the Status bar as shown below:

AVERAGEA: Calculates the average like the AVERAGE function, but with a twist. While AVERAGE ignores any text, AVERAGEA counts any text as a member contributing zero to the sum. This is clearly seen below where for the selection below, AVERAGE returns 2.5 and AVERAGEA returns 2.222222222.

For the selection below, AVERAGE function returns the same value 2.5 whereas AVERAGEA returns a different value because boolean values are treated as numbers by AVERAGEA. TRUE is treated as 1 and FALSE as 0.

AVERAGEIF: Calculates the average of contents in a range of cells based on some criteria. Example is shown below:

Only those value that qualify the condition that value is not equal to 2 or 3.

AVERAGEIFS: Similar to above function. But, we can give multiple criteria. Example is shown below:

CORREL: Calculates the correlation of two arrays containing the same number of elements. Example is show below:

COUNT: Calculates the number of cells having numbers only. See example below:

COUNTA: Calculates the number of cells having any content. See example below:

The reason COUNTA returns 5 is because cell E4 has a blank space.

COUNTBLANK: Calculates the number of blanks in the cells. See example below:

Following from the last example, result is 1 because cell E4 has a blank space.

COUNTIF: Calculates the number of cells having a content based on a condition like we saw in the case of AVERAGEIF. See example below:

The number of cells containing FOO is counted here.

COUNTIFS: Similar to COUNTIF, but based on more than one condition like AVERAGEIFS.

COVARIANCE.P: Calculates the covariance of a population. See example below:

COVARIANCE.P: Calculates the covariance of a sample. See example below:

DEVSQ: Calculates the sum of the squares of the deviations from the average  See example below:

FORECAST, FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY, FORECAST.ETS.STAT, and FORECAST.LINEAR are functions that can be used for prediction analysis where future data can be predicted based on historical values.

FREQUENCY: is a wonderful function that can be used to classify raw data into bins. As an example, see below numbers where numbers 1 to 9 and they are classified to 4 bins:

less than 3;
between 3 and 6;
between 6 and 9;
greater than 9;

Enter the formula as shown below:

On pressing Enter, the function returns 3 that is in the first bin (less than 3). To get the number of elements in the other bins, after selecting the cell containing the function, drag the selection down such that the selection equals the number of bin. Here, since there are 4 bins, we select 3 cells below the cell containing the function as shown below:

Once this is done, then, click on F2 and, then, CTRL+Shift+Enter. This will populate the selected cells with the number of elements as shown below:

GEOMEAN: Calculates the geometric mean of an array of numbers. See example below:

GROWTH: Predicts a value based on the assumption that the growth is exponential.

HARMEAN: Calculates the harmonic mean of an array of numbers. See example below:

INTERCEPT: Calculates the point at which a line will intersect the y-axis when given existing y values and x values. See example below:

KURT: Calculates the kurtosis of an array. See example below:

LINEST: is a line fitting function and calculates the best straight line.

LOGEST: is a curve fitting function and calculates the best exponential curve.

MAX: Calculates the maximum value from an array of numbers. See example below:

MAXA: Calculates the maximum value from any array of numbers, strings and boolean values.

MAXIFS: Calculates the maximum value in an array of numbers based on a criteria.

MEDIAN: Calculates the median of an array of numbers. See example below:

The rest of the functions will be covered in a following post.

Addition of Graph to Backtesting in Excel

Further to the post on backtesting using Excel, we will add a nice graph that shows the plot of SMA and LMA. Like in the last post, we will work with Excel 2016 for this post as well.

There are at least two reasons why we should be looking at graphs:

1) They are visually appealing. Graphs communicate better than just data.

2) They can be used as a first check to see if something is amiss.

We will continue with the results of this post. Adding the graph is quite simple with the following steps:

1) Add a new worksheet called Graph to the excel file by clicking + button as show below:

2) Then, click on Graph worksheet and invoke the graphing feature as shown below:

3) Right click the empty area in the graph and click on Select Data as shown below:

4) Then, click on appl worksheet and select data in Date, SMA and LMA columns as shown below:

5) Then, click on OK button to confirm the data source

6) This will take one to the Graph worksheet as shown below:

7) Then, extend it as shown below:

8) Click on y-axis and Right Click to select Format Axis as shown below:

9) Enter 80 in Minimum as shown below:

This will alter the graph and it will be more informative on when the SMA line intersects the LMA line.

10) Then, click the + sign and check the box next to Axis Titles and Legend as shown below:

11) Change x-axis title to Date and y-axis title to SMA and LMA. Change the Chart Title to SMA & LMA versus Date as shown below:

12) Lastly, two boxes with text as "BUY" and "SELL" have been added to show the time line around which the transactions happened

The graph looks okay. This concludes the addition of graph to backtesting of Excel post.

Backtesting Trading Strategy in Excel

In this post, we will take a brief look at backtesting a trading strategy in Excel.

Excel is the tool of choice for most Financial Analysts. So, it only natural that we will use Excel for backtesting. Besides, there are many features in Excel that can be used for data analysis. In later posts, we will explore some of these features. Excel also has a programming element called Visual Basic for Applications (VBA). It is assumed that you already are familiar with Excel and its basic features. For all the work in the post, we will use Excel 2016.

For this post, we will consider one of the simplest strategies, Moving Average Crossover strategy using Simple Moving Average. The strategy involves calculation of two simple averages of the closing price and generating trade signals when the Short Term Moving Average(SMA) crosses the Long Term Moving Average(LMA). If the SMA is lower than LMA and crosses LMA to a higher value than LMA, then, a buy signal is generated. Similarly, if the SMA is higher than LMA and crosses LMA to a lower value than LMA, then, a sell signal is generated.

Following are the steps for back testing using this strategy:

1) Getting the data that will be used for back testing

Two common sources of getting historical data are Google Finance and Yahoo Finance. For our example, we have downloaded Apple Inc stock prices on NASDAQ for 2016 from Google Finance. After downloading, you can open the data files in Excel as shown below:

2) Calculating the Technical Indicators as SMA and LMA

We will consider SMA of 5 days and LMA of 20 days

The formula for 5 day SMA is using AVERAGE function on the closing price. Since the first four values will not be available, we will calculate it separately and enter the data manually as we will lose out on data points otherwise.

The formula entered in G6 will be AVERAGE(E2:E6). This can then be extended to the rest of the SMA column. Screenshot is shown below for reference.

Similarly, LMA is calculated but will use the formula AVERAGE(E2:E19) and entered in H21. This formula again will be extended for the rest of the LMA column. Screenshot is shown below for reference.

3) Once the Technical Indicators are calculated, Trade Signals can be generated

A Buy signal is generated if the SMA is lower than LMA and crosses LMA to a higher value than LMA. A Sell signal is generated if the SMA is higher than LMA and crosses LMA to a lower value than LMA. The formula is used is:

=IF(AND(G2<H2,G3>H3),"BUY",IF(AND(G2>H2,G3<H3),"SELL",""))

and is to be entered in cell I3. This formula can then be extended to the rest of the column.

If you are on Excel 2016, then, according to the Microsoft documentation, you should be able to use IFS on the lines described below:

=IFS(AND(G2<H2,G3>H3),"BUY", AND(G2>H2,G3<H3),"SELL",TRUE,"")

Per the documentation, Microsoft claims that IFS function is applicable to Excel 2016. However, I do not see it in the Excel 2016 that I bought, so, let us park this discussion here.

A screen shot with the Trade Signal formula is shown below:

4) Trade Price is next calculation

The logic is quite simple: If we have a trade signal of BUY or SELL, then, we will use the Open Price of the next day. If not, then, we carry over the previous value of the Trade Price.

Below formula is entered in cell I3:

=IF(I2<>"",B3,J2)

This formula is then extended to the rest of the column. Screenshot is shown below:

5) Returns calculation follows the Trade Price

 For the purpose of calculating returns, we will assume that we deal with 1 unit at the start of the transaction. In our case, it will be a buy on 22nd February. Then, after that, we deal with 2 units, that is, we have a position on 1 unit at any point. Also, we discount any brokerage or transaction charges that may accompany the trades.

The below formula for the returns is entered in J3:

=IF(AND(I2<>"",J2<>0),IF(I2="BUY",1-J3/J2,J3/J2-1),"")

The column formatting can be converted to percentage. Formula is shown below:

6) After the Returns are calculated, calculate Total Returns 

As we see many blanks in the excel sheet where no trades have occurred, we copy the Returns to another sheet and remove the blank rows as described in the steps below:
 
a) Select the Returns columns as shown below:

b) Click F5

c) In the Dialog Box titled Go To, click Special button

d) In the Dialog Box titled Go To Special, click on Blank Radio Button and click on OK button

This will select all blank rows in the worksheet

e) Right click on selected column and click Delete as shown below

f) Select Entire Row Radio Button in the Dialog Box titled Delete

This will delete all blank rows

Then, as a last step we calculate the Trade metrics as shown below:

Total Positive Trades = COUNTIF(A2:A11,">0")

Total Negative Trades = COUNTIF(A2:A12,"<0")

Total Trades = COUNTIF(A2:A11,"<>""")

Total Returns = =SUM(A2:A11)

The final result is shown below:

It is heartening to note the positive returns.

In future posts, we will see if we can improve on the trading strategy described.