IDSB Banner

Use of Statistical Functions in a Quantrix Banking Model


In this Modeler note, a Quantrix model is built using statistical functions to maximize the return of a portfolio with a given level of risk. This analysis is important to financial planners as they evaluate and execute purchasing decisions for their customers.  Quantrix Modeler offers the statistical functions that you would expect when you need to perform complex financial analysis.

The following statistical functions to analyze our stock portfolio:
CORREL - Correlation coefficient between two data sets.
COVAR - Covariance, the average of the products of deviations for each data point pair in two data sets.
INTERCEPT - Calculates the point at which a line will intersect the y-axis by using a best-fit regression line plotted through the known x and y values.
SLOPE - Slope of the linear regression line through the given data points.
STDEV - Standard deviation based on a sample.

In this model, we will use the daily price and return data for five stocks for the year 2000.  In this model, we will use the daily price and return data for five stocks for the year 2000. This is represented in the Stock Data Matrix in Quantrix.
ADM – Archer Daniels Midland Co.
IBM – International Business Machines
MAY – May Department Stores
KO – The Coca-Cola Company
XOM – Exxon Mobil Corporation

You can calculate the daily return and average daily return for all the stocks in the portfolio using one recurrence formula. Inspect formula #4 in the Investment Schedule Matrix. In plain English, today’s [THIS] date’s daily return equals the closing price of today’s [THIS] date less the closing price of the previous [PREV] date divided by the closing price of the previous [PREV] date. Using this language, a formula can be written that will calculate all of the daily return prices for each stock for each day. 

This one formula calculates over 1200 cells!

Date[THIS]:Daily Return = (Date[THIS]:Closing Price-Date[PREV]:Closing Price)/Date[PREV]:Closing Price skip Average


Then at the bottom of the matrix an average(summary) formula is used in formula #3 to calculate the average daily return for the year. The covariance and correlation of the stocks are calculated in the Correl & Covar matrix.A SELECT function is used to calculate all the data with only one matrix. Inspect the formulas in the matrix to see the structure of the formulas. Again, the use of Quantrix formulas allows you to write only 2 formulas to calculate the entire matrix, and if stocks are added or deleted from the analysis the formulas will not have to change. From this analysis we can determine that KO and ADM have the highest correlation coefficient of 0.254. These stocks have the closest relationship to one another. Using this analysis as a basis, we can then build matrices to address the following:

Consider an initial investment of $1000 that has been spread equally across the five stocks at the beginning of the year. At the 125 day mark, evaluate the returns and redistribute the total money you have presently in your accounts into the stocks. Compare this scenario with a scenario where the same portfolio was not re-allocated at mid-year. Also determine the expected return and the standard deviation in daily return for the portfolio. *

In the Quantrix model, the final answer is contained in the Investment Summary Matrix. The summary matrix draws its information from the analysis performed in the Investment Allocation and the Investment Schedule Matrices. In the end, it is determined that for this example it would have been better to leave the money in for the whole year instead of reallocating the stocks in the middle of the year. We can then calculate the Linear Regression of the portfolio and graph the results. This is shown in the Linear Regression Calculation and Linear Regression Table Matrices. We used Quantrix functions to calculate the intercept, slope, standard deviation and r-values. The linear regression is then calculated in Quantrix by multiplying the slope with the day of the year, then adding that result to the intercept value. The linear regression and daily stock fluctuations are then charted in the chart views from the Linear Regression matrices.


However, it can be determined that the linear regressions in general do not provide the best means for modeling the actual stock trends. Most of the correlations are so low that one could not be confident to predict the best fits form their regression lines.

 

XOM is the only stock with a R-Value greater than .5 which shows that this is the only stock that is well-estimated by the regressions. The least risky stock is likely to be XOM. It has the highest R-Value (.71) and the lowest standard deviation (.0199). The most risky stock is ADM. Although it is the most profitable stock at the end of the analysis, it also shows that it is the most volatile stock. Based on the data given, it has a small R-Value and a high standard deviation.

This analysis is a powerful example of the advantages of Quantrix. Only 28 plain English formulas are needed in Quantrix. The same analysis in a traditional two-dimensional spreadsheet requires the use of over 6000 cell-based formulas. Additionally, the Quantrix file size is more than 7 times smaller under the same comparison. In addition, new stocks could be added to this model without changing one single formula! Using Quantrix allows you to focus more on the investment decision as it requires much less time troubleshooting and debugging cell based formulas. Clearly, Quantrix is a powerful tool for decision makers in the financial industry.

Downloads:

    >> Banking Model

 *This analysis was based on an Investment Banking problem paper from the Industrial Mathematics Program at Worcester Polytechnic Institute. More information about this program can be found by clicking here.
idbs_banner_960x41
About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -