Change text size

• Email This Article
• Print This Article
• Digg This
• Delicious
• Subscribe

Similar to investing, many individuals have a preferred analysis method or tool to review their finances. If you’re seeking ways to customize your analysis, spreadsheets offer lots of flexibility. This article provides tips for analysis using Excel and makes use of the specific formula formatting from Microsoft. When in doubt use the formula dialog box to clarify the inputs.

Although other spreadsheets have a different general format for formulas (i.e. use of @ versus =), many of the commands listed are similar or the same. Needless to say, this is a short-list of what’s available.

Basic Tips & Formulas

As a very basic rule of thumb, be sure to set-up the spreadsheet in a logical manner, typically with records displayed as rows. Each column represents a specific field for the records with subsequent columns available for additional calculations. And as a quick reminder if you’ve been away from this for awhile, use parenthesis to identify the order operations particularly when you want to add or subtract data first, then multiply or divide it by another column.

Data Ranges are identified using the cell that begins and the cell that ends the series: (A3:A10) for the third through tenth cells in column A.  Unless stated otherwise, assume data appears in column A.

Formulas:

(A21):  =SUM(A3:A20), adds values in A3 through A20
(A21):  =COUNT(A3:A20), identifies the total number of numeric entries in A3 through A20
(A21):  =COUNTA(A3:A20), identifies the total number of character entries in A3 through A20

These basic statistical functions are pretty self-explanatory in terms of the result yielded and come in handy when reviewing columns of data:

(A21):  =AVERAGE(A3:A20)
(A21):  =MEAN(A3:A20)
(A21):  =MEDIAN(A3:A20)
(A21):  =STDEV(A3:A20)
(A21):  =VAR(A3:A20)

To measure the magnitude and extent of the relationship between two sets of data with the same number of entries, you can use the correlation formula. Assuming column C has S&P 500 returns and column E has your portfolio returns, you can measure the relationship between the two as follows:

(F3):     =CORREL(C3:C100,E3:E100)

To obtain the coefficient of determination between the two you can square the CORREL result:

(F4):     =POWER(F3,2)

When the coefficient of determination is 0.65 (“-ish”) or more, there is a strong relationship between the two sets of data. This translates to about +/- 0.80 for correlation. Expect to see data organized in a linear fashion when there is a strong relationship between the two data series if you use a scatter plot.

Keep in mind that daily market data is often associated with more noise so you may need to use weekly data to obtain meaningful, bigger trend relationships for return data.

Quick Data Views

Different charts and graphs can be created pretty readily with dialog boxes that prompt you to identify the data. Line charts can be used for closing value or return data, but don’t forget to use scatter plots when you want to see if the results generally cluster in some uniform value or if there are certain results that seem way out of whack.

Scatter plots are especially helpful at providing visual statistical information (mean versus median and linear relationships) including the presence and impact of outliers. Once the scatter plot is created, you can add a linear trend line which is the line of best fit for the data. From this you can get the formula of the line, including slope, intercept and coefficient of determination data (R²).

Not seeing the benefit yet? If you plot S&P 500 Index [SPX] returns against your portfolio returns, you can determine your individual portfolio beta by plotting this regression line and obtaining the slope of the line:

SPX return = (Portfolio Beta * x) + b.

Consider also checking out histograms for another data view along with pie charts for big picture information.

Categorizing Data

Using the “if” function is a great way to categorize data under broader groupings. As an example, suppose you have fifteen years of monthly performance data and wish to perform one calculation if the month had positive returns and a different calculation if there were losses. You can use the if-statement to perform one calculation or the other, or to categorize the data if you wish to perform multiple calculations on the two distinct results.

The if-statement includes the cell being tested and the criteria for the test, the result if the test is true, and the result if the test is false. Formatting is as follows: =if(cell reference with test, value if true, value if false) and can include text characters. An example provides much more clarity.

Example: Assume return data appears in column C (starting in C3) and you wish to quickly determine the total number of profitable months and complete additional calculations based on whether there were profits or losses. You can use an if-statement in column D as follows:

(D3): =if(c3>0,1,0)

When C3=5, Result: 1

The sum function can be used at the bottom of column D to quickly calculate the total number of months with profitable returns. Other logical statements are also available. Think of the if-statement as an If-Then-Else command in programming.

Financial Formulas

Thanks to a friend in an MBA program, I have the pleasure of reviewing introductory finance materials. I may need to upgrade out of jeans for the dinner that will be required as a result. But seriously, one of the best reminders from the process is the Net Present Value approach to valuation using future cash flows. The NPV function in Excel will allow you to determine the current value of future payments assuming you have interest rate data for each of the years such payments are in effect. Multiple formulas can be used for varying rates of return.

=NPV(interest rate,CF1,CF2, …), where CF is the Cash Flow value in year 1, 2 and so on.

There are a myriad of other financial functions, but this seems to be the one most applicable to financial return data. Look for coupon, duration and other investment formulas in the help guide or library.

Macros

One of the best introductions to Visual Basic, the programming component for Excel, is to record a macro then edit it. Visual Basic [VB] sets up the language mirroring the commands and movement you just completed. This provides you with a nice glimpse of VB formatting and syntax. If nothing else, playing around with the macro function may reduce the time it takes you to compile data by minimizing the key strokes needed to perform the command(s).

Hopefully this provides you with some useful, additional ideas for reviewing and evaluating your results. As a final comment, www.wikipedia.org provides some great summary information on statistical and financial terms to help you through the process.

To access other articles written by Clare White, please click here.

Clare White
Contributing Writer and Options Strategist
Optionetics.com ~ Your Options Education Site
Questions for Clare? Visit the Optionetics.com Discussion Board

• Email This Article
• Print This Article
• Digg This
• Delicious
• Subscribe

Recent Articles by Clare White, CMT, Optionetics.com

• August 15, 2008
  Analytical Toolbox: Varying Indicator Settings
• August 7, 2008
  Platinum Tools: Probability Features—Getting Started
• July 31, 2008
  Analytical Toolbox: Market Outlook—Bounce or Reversal?
• July 24, 2008
  Analytical Toolbox: Trade Psychology—Andrew Cardwell, Part II
• July 18, 2008
  Analytical Toolbox: Speaking with Andrew Cardwell, Part I