## Using Excel Ebook

## Hidden Secrets In Microsoft Excel

This ebook from Francis Hayes gives you professional tools to help you get the most out of your Excel program. Any one of these secrets could be the only one that you ever need to know for Excel, but this ebook includes bunches of those tips and tricks! Just think of all of the useful information you can get from it! If you have ever been frustrated at your lack of progress in Microsoft Excel, this guide will teach you everything that you need to know to harness the powerful functions or time-saving elements of Microsoft Excel. Excel is used by offices all over the world, but so few people take the time to teach you anything important about it. Too much time is usually wasted searching the internet for tips on how to use it more efficiently Learn the best way to master Excel in this ebook!

#### 101 Secrets of a Microsoft Excel Addict Summary

Contents: Ebook

Author: Francis Hayes

Price: $9.97

#### My 101 Secrets of a Microsoft Excel Addict Review

I've really worked on the chapters in this book and can only say that if you put in the time you will never revert back to your old methods.

All the modules inside this book are very detailed and explanatory, there is nothing as comprehensive as this guide.

## Importing Data into Excel

You may be asking yourself Why Excel Well, my friend, even if you have an advanced program that already calculates pairs, Excel will do wonders in helping you understand the internal workings of the strategy. This chapter is meant only to assist investors in creating a basic Excel spreadsheet for pairs analysis and learn how to import free (yes, free) end-of-day data from Yahoo.com. Also, the reason for a brief chapter on Excel is that we will be doing many of our calculations inside the spreadsheet program. In general, almost everyone has access to Excel thus, it serves as an easily accessible platform to convey the pairs subject. Step 1. Open Excel (any version will do) and create a new spreadsheet. Type Main Spreadsheet 1 in cell A1, and then save the sheet as Exam-ple_pairs_sheet1. We will continue adding to this sheet as you work through the book, so by the end you will have your own spreadsheet. (As a side note, you will need Internet access to build your spreadsheet.) FIGURE...

## Excel Application 141

Excel provides two functions XIRR (values, dates, guess) and IRR (values, guess) that enable internal rates of return to be calculated quickly. If we enter IRR(A1 A6, 0.1) in cell B1, Excel calculates the internal rate of return of 2.9 per annum. The guess used in this case (0.1) is the default value. This works in most cases, but it is useful to change it to several different values, including negative values, if the cash flows are large relative to the initial and final values, or if the answer is expected to be a negative IRR. For example, suppose that the cash flows in the example above occurred in the middle of each year. We would then need to set up two columns in Excel. The first column could contain the dates the second, the values. So, in our example, we would enter 1 Jan 1997, 30 Jun 1997, 30 Jun 1998, 30 Jun 1999, 30 Jun 2000, 30 Jun 2001 and 31 Dec 2001 in cells A1 to A7 and the values 1.5, 0.1, 0.2, 0.8, - 0.3, - 0.1, - 2.5 in cells B1 to B7. If we enter XIRR(B1 B7, A1...

## Adding a trendline to an Excel candlestick chart

Another function you can enjoy when using Microsoft Excel for charting is the trendline function. It's found in the same area as moving averages (see the preceding section), so if you can add a moving average, you can easily add a trendline. A trendline is a line that indicates the direction of a trend either higher or lower. The line is usually drawn based on the lows (in the case of an uptrend) or the highs (based on a downtrend) of the price action in the trend. For more information on trendlines and how to interpret them with candlestick charts, turn to Chapter 11. 1. With your chart sheet open in Excel, select Chart on the menu bar. A menu box will open. If you've continued using the data from the previous examples in this chapter, your Excel chart with an added trendline should resemble Figure 4-8. A basic Excel candlestick chart with a trendline added. A basic Excel candlestick chart with a trendline added.

## Matrix Algebra In Excel When The Number Of Assets Increases

From the previous section we can see that modelling portfolio risk within Excel using matrices rather than the linear version of the risk equation appears far more convenient and flexible. The file Laws002.xls includes data on equity indices from seven European countries, including the UK and forms the basis of all operations in this section. As before, this file contains a number of worksheets, the titles of which are self-explanatory. We are going to utilise this dataset to show how portfolio analysis for portfolios with more than two assets can be modelled within Excel.

## Excel Application 151

Excel provides a significant number of useful functions for bond analysis. If the functions are not available, you will need to run the Setup program, install the Analysis Tool Pak and enable it using Tools Add-Ins. Two of the relevant Excel functions are Yield (settlement, maturity, rate, pr, redemption, frequency, basis) which returns the yield on a security that pays periodic interest and MDuration (settlement, maturity, coupon, yld, frequency, basis) which returns the Macauley modified duration for a security with an assumed par value of 100. In the yield formulae

## Real Options Analysis Toolkits Function Description for Excel

These functions are available for use in the full version of the Real Options Analysis Toolkit software. Once the full version is installed, simply click on Start, select Programs, then Crystal Ball and Real Options Analysis Toolkit. Next, select Functions. The software will be loaded into Excel, and the following models are directly accessible through Excel by typing them directly in a spreadsheet or by clicking on the Equation Wizard and selecting the Financial All categories. Scroll down to the RO section for a listing of all the models. This is the Customized American sequential phased compound option exercisable at any time up to expiration, where the value of the option depends on a series of up to four other phases, occurring in sequence. Each option phase has its own asset value, volatility, implementation cost, and different implementation times. In addition, at any phase, there is an option to execute the expanded phase, abandon, or contract. Please note that this function...

## Numerical Errors in Excel Software

Consider the familiar Microsoft Excel 2000 (v. 9.0.2720) package. Place numbers 1,2, and 3 in the first column, and use STDDEV function to compute the standard deviation of these numbers. The correct answer is 1. Now add 99,999,999 to each number. Clearly, the standard deviation should not change by addition of any number. However, Excel gives the answer of zero It appears that as long as the numbers are smaller than 100 million, Excel works all right. This is a serious problem with such popular software, which was pointed out to Microsoft years ago and still has not been fixed. One explanation is the monopoly that Microsoft enjoys, and perhaps a more potent explanation is that the commercial users of the software do not care enough about numerical accuracy.

## Excel Application 161

Excel provides a useful function ( Goal Seek'') that does just that. As an illustration, consider a European call option with strike price 105, term to expiry of six months, a risk-free rate of return of 5 per annum (continuously compounded) and assume that the current price of the underlying asset is 100. We shall assume that the option is priced in the market at 2.5. The process involves setting up the Black-Scholes formula in a cell on a spreadsheet and then letting Excel search for the value of the volatility that makes the Black-Scholes price equal to the observed market price of 2.5. Then, assuming that the Black-Scholes model is correct, we will have an estimate of the volatility. Enter a guess for the volatility, say 10 , although any value will do, in cell B2. Enter the risk-free rate of 5 in cell B3 the time to expiry of 0.5 in cell B4 the current price of 100 in cell B5 and the strike price of 105 in cell B6. The Black-Scholes formula, B5 *NORMSDIST(D3) -B6*EXP(-B3*B4)*...

## Microsoft Excel Steps for Computing Regressions

This step might not be needed. In Excel Tools menu see if you see data analysis as an option, if not, click on Add-ins of the Tools menu. If you click into the box on the left side of Analysis ToolPak, it places a check mark there. Similarly, click along the box on the left side of Analysis ToolPak VBA. Finally, click on the OK button to get out of the menu. Step 3. Click on Tools menu, then on Data Analysis Next, highlight Regression and click OK. Step 4. Fill in the cell range for Input-Y range Next, fill in the cell range for the regressor in Input-X range area. Check the box for Labels if data labels are present. Choose starting cell location where the output should appear, making sure that there is enough blank space to the right. Excel output gives most of the needed estimates regression coefficients, their standard errors and confidence intervals, adjusted R2 for assessing the overall regression fit, and so on. Consider a multiple regression model y a...

## Excel Application 171

The use of Excel spreadsheets alone is not a very efficient way of implementing a large-scale asset-liability model and is not to be recommended under current technology. However, there are some Add-In products (such as Risk from Palisade Corporation) that can be used to construct models. In addition, implementing models using Excel VBA (Visual Basic for Applications, the macro language associated with Excel) is often fast enough for many practical purposes. Nevertheless, an Excel spreadsheet can be used to generate sample projections that will enable you to get a feel for the differences between the types of models considered in this chapter. As an example, we show how to create a sample path for an inflation index (Q(t)) that has the following structure Do not be too disconcerted if you discover NUM errors in your simulated series. This just indicates that the model has ended up generating rates of inflation that are too extreme for the numerical precision in Excel. Press F9 a...

## Adding a moving average to an Excel candlestick chart

If you're interested in dressing up your Excel candlestick charts with some added information, moving averages are a good place to start. A moving average is the average of the closing prices for today and looking back a certain number of days. For instance, a five-day moving average would be the closing today added up along with all the closing prices of the previous four days with that total divided by 5. The process is easy, and the result gives you that much more room to analyze and interpret. (Flip to Chapter 11 for more on moving averages.) Follow these instructions to add a moving average to your Excel chart 1. With your candlestick chart sheet open in Excel (see preceding section), select Chart on the menu bar, and a drop-down menu box opens. A basic candlestick chart created using Microsoft Excel. A basic candlestick chart created using Microsoft Excel. HftNG Notice that the moving average doesn't start until the fifth time period. This is not usually the case in...

## Building an Excel candlestick chart

Excel is a great way to get your feet wet building a chart. It also gives you some experience as to what's behind the chart. Commercial packages that do all the work for you don't give you that type of experience. At the time of this writing, I used Excel 2003, but I was just starting to work with the 2007 version. The 2007 version is quite a change from the 2003 version and is a little different to use. However, there are added features in the 2007 version that make it worth your while. Also, eventually, this new version will be the standard, and you won't have a choice. For now, as most people still seem to have the 2003 version, the examples use that version. Congratulations You're now the proud owner of an Excel candlestick chart. Feel free to right-click the chart to view all the options available for your tinkering pleasure. For example, when you right-click one of the black candles, a Format Down Bars box opens, and you can change the color of all down bars (bars for days when...

## Adding volume data to an Excel candlestick chart

The most difficult type of add-on for Excel candlestick charts is a volume chart. There are a lot of additional steps involved with this operation, and if it gets to be too taxing, you may want to leave the volume charts to the free online charting services described earlier in this chapter or the charting packages discussed at the end of the chapter. Now you can make a chart with volume The steps are very similar to creating the basic Excel candlestick chart A basic Excel candlestick chart with volume before final adjustments. A basic Excel candlestick chart with volume before final adjustments.

## Creating Candlestick Charts Using Microsoft Excel

Microsoft Excel is an excellent tool for running all sorts of financial analyses. One of the great features of Excel is its charting tool, and, of course, that tool includes candlestick charts as one of its choices. In this section, I explain the process for creating a candlestick chart with Excel, from finding and entering the data to building the chart. I even clue you in on a few ways to add some additional information to your Excel candlestick charts, including moving averages, trendlines, and volume data.

## Excel Application 172

We now need to set up some random numbers so that we can construct random quarterly returns for the derivative product. To do so, we can use Excel's random number generator. You will find this in the Tools Data Analysis drop down menu (if this option is not available, you will have to use Tools Add Ins to load the Data Analysis Add In). Under Tools Data Analysis, click on Random Number Generation in the option list. This will then provide you with an input form. In the box for number of variables, enter 3 (we will need 3 months to generate each quarter). In the box Number of random numbers'' enter 1000 (this is the number of bootstrap samples that we will be using in this fairly small scale bootstrapping exercise). Under Distribution select the Uniform distribution since we want each month to have an equal chance of being selected. In the parameters section, enter 1 and 25 for the range of values. Click the radio button next to Output range and enter D2 in the space provided. Then...

## Annex 121 Using Excel to calculate efficient frontiers Solver solution

Several commercial providers have developed software that enable one to construct efficient frontiers based on various definitions of risk and with a variety of constraints. But it is helpful to be aware that one of the standard Add-Ins that accompanies Excel, namely Solver, provides a method for calculating efficient portfolios. You first have to check that Solver has been installed with your version of Excel as it is often not installed in a typical installation . You can check by looking in the Tools dropdown menu and see if Solver appears as one of the options. If not, you can try to include the Add-In yourself click on the Tools Add-Ins menu. You should see a pop-up box with various Add-Ins and check boxes that indicate whether or not they have been loaded. If the Solver Add-In is one of the options, then check the appropriate box. If Solver does not appear in the list, then you will need to load it from the original Excel Installation CD-ROM. We now need to set up the problem in...

## Quick Back Testing with Excel

Initial back-tests can be easily done in Excel. Simply paste your historical time series into Excel, enter your formula, and apply it to all cells in the time series. The easiest way to express this is by assigning each type of market position by a -1 (sell), 0 (out of market), or a 1 (buy). Then calculate profit or loss, subtracting out a spread and or transaction cost. I recommend mastering Excel thoroughly before buying an expensive back-testing tool. This ensures that you know how back-testing works from the ground up.

## Excel Application

Another very useful pair of functions in Excel are XNPV and XIRR, which can be used to calculate net present values and internal rates of return, respectively. Example 1.8 can be replicated in Excel by entering the following dates in cells A1 to A4, 1 Jan 2002, 31 Dec 2004, 31 Dec 2009, 31 Dec 2013 and the following amounts in cells B1 to B4, -2.2386, 1.2, 1.5 and 2.0. If the formula XIRR(B1 B4,A1 A4,5 ), it returns the answer 0.09996, which is extremely close to 10 .

## Preface to the Fifth Edition

This edition catches up with recent developments in financial statement accounting and financial reporting. All exhibits have been refreshed to make them easier to follow and more relevant. The exhibits in this edition are typeset from printouts from Microsoft Excel work sheets I have prepared. To request a copy please contact me at my e-mail address tracyj colorado.edu.

## Tools And Materials Needed For The Scientific Approach

Not only is a universe of data needed, but it is necessary to simulate one or more trading accounts to perform back-testing. Such a task requires the use of a trading simulator, a software package that allows simulated trading accounts to be created and manipulated on a computer. The C+ + Trading Simulator from Scientific Consultant Services is the one used most extensively in this book because it was designed to handle portfolio simulations and is familiar to the authors. Other programs, like Omega Research's TradeStation or System Writer Plus, also offer basic trading simulation and system testing, as well as assorted charting capabilities. To satisfy the broadest range of readership, we occasionally employ these products, and even Microsoft's Excel spreadsheet, in our analyses.

## Word On Notation Used In This Book

In attempting to make the contents of this book more practical for many readers, there are three types of notation that can be found mixed together. Of course, the standard mathematical formulas for most methods appear as they had in the previous editions. Added to that are spreadsheet examples, using Corel's Quattro code, which is very similar to Microsoft's Excel. Readers should have no trouble transferring the examples found here to their own choice of spreadsheet.

## Portfolio Optimization

In the special case in which an investor's utility function is quadratic, a simpler approach can be employed. As we have shown, such an investor will be concerned only with the mean and variance of portfolio return. Efficient computational procedures have been developed for selecting portfolios under such conditions using only the expected returns for the securities, the standard deviations of their returns and the correlations among the returns. Simple cases can be solved using the gradient method of Sharpe 1987 . The critical line method developed by Markowitz 1952 can be utilized for more general problems. The solver procedure included with Microsoft's Excel program may also be utilized.

## Appendix Review of Matrix Multiplication

Matrix multiplication can be easily implemented in Excel using the function MMULT . First, we highlight the cells representing the output matrix C, say fi g2. Then we enter the function, for instance MMULT(a1 c2 di e3) , where the first range represents the first matrix A, here 2 by 3, and the second range represents the matrix B, here 3 by 2. The final step is to hit the three keys Control-Shift-Return simultaneously.

## Making money in the Futures markets

Just remember, making money in the futures market won't happen overnight. You will have to apply yourself, study the markets and charts, make a game plan and follow your plan consistently. You will also have to be conservative. It's a lot like a conventional retail business, where you learn all about the business, study the ins and outs and seasons, formulate a business plan, and open up for business. Once you start trading you should keep good accounting records so you can track your expenses, profits and losses. Tracking your trades can be done through accounting software, such as Quicken or a simple Excel spread sheet, this is important because you should be able to compare your records to your brokers statements It also helps you to calculate your overall returns. Try not to get greedy. Greedy people often lose out and don't profit at all. No one likes to study or have to figure out charts, formulate plans, and have patience and management right Unlike other businesses that...

## Why a New Thinking Methodology1

Perhaps many of you reading this book have heard about a seminar being offered where you can learn how to walk barefoot over a 20-foot bed of red-hot coals. The people who developed the method to make it possible did so on the assumption that the achievements of people who do things very well and excel beyond what other members of the same culture of society would consider possible do so as the result of a specific way they think- a methodology in which their beliefs are in some way different from everyone else's. This methodology can be broken down into a system that can be learned and subsequently taught to others. The only difference between those who excel and those of mediocre achievement is that one group has learned a thinking methodology that has not occurred to the other. Now, I'm not going to have you fire-walking the futures pit, but habits of thought die hard. And to make way for the new thinking methodology I offer as a means of excelling as a trader, you will have to...

## Preparing for the long term

Stock investing is best suited for making money over a long period of time. When you measure stocks against other investments in terms of five to (preferably) ten or more years, they excel. Even investors who bought stocks during the depths of the Great Depression saw profitable growth in their stock portfolios over a ten-year period.

## Skills To Be Acquired

To excel in any activity whether it is mental, such as trading, or physical, such as swimming we need to learn specialized skills. These skills give us the necessary requirements to look at, think about, and behave toward events in a manner different from what we may be used to or what we may have been taught. However, beyond the sheer mechanics of the activity which just about anyone can master lies a particular thinking methodology or strategy that leads to excellence. Although few people have it, such a thinking methodology can nevertheless be learned.

## Technical Analysis Indicators

Getting into more complicated technical analysis, we will begin to look at indicators as an additional way of examining the present stock price, while deciphering historical price movements. Each of the indicators in this chapter can be applied to individual stocks, while also being used in conjunction with actual pairs. However, not all technical analysis programs offer the ability to track spreads or apply indicators to either a differential or ratio. Therefore, later in the book we will discuss how to build indicators in Excel. What's more, in Appendix II you will find a list of technical analysis programs that are pairs friendly, with a brief description of each.

## Optimizer Tools And Information

Although sometimes appearing as built-in tools in specialized programs, genetic optimizers are more often distributed in the form of class libraries or software components, add-ons to various application packages, or stand-alone research instruments. As an example of a class library written with the component paradigm in mind, consider OptEvolve, the C+ f genetic optimizer from Scientific Consultant Services (516-696-3333) This general-purpose genetic optimizer implements several algorithms, including differential evolution, and is sold in the form of highly portable C+ + code that can be used in UNIX LINUX, DOS, and Windows environments. TS-Evolve, available from Ruggiero Associates (800-21 1-9785) gives users of TradeStation the ability to perform full-blown genetic optimizations. The Evolver, which can be purchased from Palisade Corporation (800-432-7475), is a general-purpose genetic optimizer for Microsoft's Excel spreadsheet it comes with a dynamic link library (DLL) that can...

## New Visitors Tour Turtle Tradercom

Why has trend following been the greatest style of trading in the past 30 years and continues to be The answer is that the Turtle system and all other trend following systems highly correlated to the Turtle Market Wizards system have worked in the past, excel today and will perform into the future for the simple reason trends exist.

## Is It All fist Random

Ccording to many academics, technical analysis is a pure waste of time. Price, they claim, is absolutely random. Using patterns and indicators to predict its behavior is no different and no less primitive than reading entrails of a dead animal in order to divine your future. One of the favorite tricks of the pure randomness crowd is to have the random function in Microsoft Excel generate a series of numbers and then plot it on a graph. Admittedly, when many technically oriented traders are confronted with a seemingly nice chart pattern only to be told later that it's all random, they experience a loss of confidence. Is it all a ruse Is technical analysis useless Are we hopelessly wasting our time trying to learn its precepts No, no, and no. What do we trade in FX I often ask this question at seminars. I get many answers but rarely the right one, for what we trade in FX is what is traded in all markets sentiment. Fundamental factors shape and manufacture sentiment, while technical...

## Using Electronic Resources to Create Full Charts

Taking advantage of free online candlestick charting Using Microsoft Excel to create a candlestick chart Considering a few charting software packages After taking a look at some free online options for candlestick charting, I present the basics of creating candlesticks using Microsoft Excel. Candlestick charting has caught on so quickly that it's now a standard feature in Excel's charting section. Finally, at the end of the chapter, I cover a few of the best low-cost charting packages that are widely available and make sense for individual traders and, to a lesser extent, investors.

## Checking out Yahoo Finances other useful features

In the blue box on the left of the page, you can find many sources of data and other handy functions. Among them is a unique feature that Yahoo provides for accessing historical daily opening, high, low, and closing price data, with volume information that you can download into Microsoft Excel using the Historical Price choice. (Skip ahead to Creating Candlestick Charts Using Microsoft Excel later in this chapter for more info on how to work with candlesticks in Excel.) To top it all off, Yahoo allows you to build your own portfolio, which you can use to monitor news and prices for any stocks you specify. You can also access real-time quotes most quotes available on the Internet are delayed for a small fee, and take advantage of excellent financial calendars for upcoming earnings and economic statistics.

## Largest Winninglosing Trade

It also is very important, from a money management standpoint, to keep all trades as uniform as possible. Therefore, it is of importance to know your standard deviation intervals. To calculate the standard deviation interval for all trades, type in the following formula in Excel

## A key Soros secret is gaining access to world leaders

Soros was amused by such thinking People are basically misguided in their view of my infallibility, because-and I don't mind stressing this-if anything, I make as many mistakes as the next guy. But where I do think that I excel is in recognizing my mistakes, you see. And that is the secret to my success. The key insight that I have reached is recognition of the inherent fallibility of human thought.

## Business Description 611 Introduction

In the business world, seed capital investors absolutely require clearly defined business goals prior to contributing capital. During the design stage, goals can drift and scope can creep if not bounded by written and well-understood documentation. In the trading world, this is often not the case when evaluating new, proprietary ideas. Often, simple Excel models are used to prove strategies without ever writing a business plan. While

## Making sure the data is in the correct format

Unfortunately, Microsoft Excel needs your data to be in a very specific format, and, of course, it doesn't come directly from Yahoo Finance in that format. You need to make some quick adjustments to make sure that your numbers are in order. 1. In your saved Excel data file from Yahoo , start by deleting the Adj. Close column.

## The CAPM and the Index Model

To illustrate the investor's problem, we first produce 24 possible observations for the risk-free rate and the market index. Using the random number generator from a spreadsheet package (e.g., you can use data analysis tools in Microsoft Excel), we draw 24 observations from a normal distribution. These random numbers capture the phenomenon that actual returns will differ from expected returns This is the statistical noise that accompanies all real-world return data. For the risk-free rate we set a mean of 5 and a standard deviation of 1.5 and

## Reasons to bug through a discount broker

You want to invest in funds from many fund companies. In general, different fund companies excel in different types of investments you may want to build a portfolio that draws on the specific talents of these various companies. Although you can buy directly through each individual fund company, there eventually comes a point where the hassle and clutter are just not worth it. The one-stop shopping of a discount broker may well be worth the occasional transaction fee.

## Test Your Understanding

You will obtain the same result with the Excel template. Note that you may find a difference of a few cents between results you get by using the table and those from the Excel model. This is because the table goes out only six decimal places, leaving the possibility of a small error due to rounding.

## Calculating Correlations Yourself

Because correlations have the tendency to shift over lime, the best way to keep current on the direction and strength of your pairings is to calculate them yourself. Although it might seem like a tricky concept, the actual process can be made quite easy. The simplest way to calculate the numbers is to use Microsoft Excel. In Excel, you can take the currency pairs that you want to derive a correlation from over a specific time period and just use the correlation function. The one-year, six-month, three-month, one-month, and six-month-trailing reading gives the most comprehensive view of the similarities and differences between pairs however, you can decide which or how many of these readings you want to analyze. Breaking down the process step-by-step, we'll find the correlation between the GBP USD and the USD CHF. First you'll need to get the pricing data for the two pairings. To keep organized, label one column GBP and the other CHF and then put in the weekly values of these...

## STEP 3 LOOP 1 Prototype Known Calculations

We know a firm that used Excel's standard functions to average the volatility between historical volatilities. That is, given several standard deviations for several stocks, they calculated the portfolio volatility as the average of the constituents. Of course, standard deviations are not additive, but no one caught it for several years. How much money do you suppose they lost because their programmers used standard Excel functions in unexpected ways

## Few Final Thoughts About Part

I his part started out by taking a closer look at which system testing measures are more useful than others, and why it could be a good idea to expand the analysis work a bit with the help of a spreadsheet program, like MS Excel or Lotus 1-2-3. To properly evaluate a trading system, it is of paramount importance to use a set of universal measures that give an equal weighting to all the trades, no matter where and when they are derived. To accomplish this, it also is important to use the right type of data. As we have seen, not all data can be used all the time knowing when to use what is vital in building a robust and profitable trading system.

## Figure 46 A histogram of the 65sma3cc system over a narrower range of profits and losses Notice that only a small

The 65sma-3cc curve is more sharply peaked than the standard normal curve. To generate a normal distribution that would fit our data, I used a Microsoft Excel 5.0 spreadsheet and employed an iterative process of manually tweaking the values. The fitted normal curve, with a mean of-0.16 and standard deviation of 0.18 is shown in Figure 4.8. The fitted normal distribution shows that the actual 65sma-3cc distribution has fat tails. This simply means that there is a larger probability for the big trades than would be expected from the normal distribution. This chart shows that unusually large profits or losses are more likely than might normally be expected.

## Black Scholes Valuation

First, we compute the present value factor, which is e-rT exp(-0.05 x 6 12) 0.9753. We then compute the value of d1 ln S Ke-rT a Jt + a Jt 2 0.2475 and d2 d1 - a Jt 0.1061. Using standard normal tables or the NORMSDIST Excel function, we find N(d1) 0.5977 and N(d2) 0.5422. Note that both values are greater than 0.5 since d1 and d2 are both positive. The option is at-the-money. As S is close to K, d1 is close to zero and N(d1) close to 0.5.

## Converting Prototypes to Coded Models

Relative to the Research and Document Calculations stage, K V Stage 1, backtesting may require a tool change from prototypes (in Excel, Resolver, MATLAB, SAS, etc.) to coded implementation of trading algorithms. In such cases, regression testing is key to achieving successful and reliable development of the software that implements the trading investment Regression testing against prototypes developed in Stage 1 can identify when codified implementations of benchmarked and prototyped algorithms fail, allowing product teams to catch errors as soon as they arise. Tool changes can have unexpected side effects that might break previously tested functionalities. Regression testing will detect hard-to-spot errors, especially those that occur because a programmer did not fully understand the mathematical or logical constructs or the internal connections between algorithms of the trading investment system. Every time code is converted from one implementation (say, an Excel cell formulas...

## How Lenders Evaluate Properties

Commercial lenders are people of a different breed. (Think about it They live and die making decisions based on Excel spreadsheets.) They look at properties from a different point of view than the rest of us investors. So, it's wise for you to understand where they're coming from when they reject your deal. In this section, we help you to understand them, and we show you how to put your best foot forward in getting your deal approved for the best loan.

## Efficient Diversification With Many Risky Assets

The Excel model Two-Security Portfolio is based on the asset allocation problem between stocks and bonds that appears in this chapter. You can change correlations, mean returns, and standard deviation of return for any two securities or, as it is used in the text example, any two portfolios. All of the concepts that are covered in this section can be explored using the model.

## Exhibit 9.12 Monthly Excess Return Data For Two Portfolios And Three Risk Factors

Problems 6-7 refer to the data contained in Exhibit 9.12, which lists 30 monthly excess returns to two different actively managed stock portfolios (A and B) and three different common risk factors (1,2, and 3). (Note You may find it useful to use a computer spreadsheet program (e.g., Microsoft Excel) to calculate your answers.)

## Looks Can Be Deceiving

We walked through a (not surprisingly) old door and out onto what was apparently George's trading floor. It was a scattered group of more old doors sitting on top of sawhorses, sitting on top of old carpet. The computer screens looked newer, but the 20 or so traders were all wearing either older suits (even the young guys) or casual clothes. The other thing that looked remarkably different was that there were an equal number of men and women here or perhaps even more women than men. All the traders that I saw had the same things on their screen an Excel spreadsheet up with a bunch of columns stretching into eternity, a set of prices flashing quotes for several currencies, and charts. The place wasn't quiet, but it wasn't a playground either. I could tell that I was standing in the midst of a focused crowd. I figured that we would sit down inside George's office for a chat, but what I didn't understand at first was that the entire room had no private office. It was just one room,...

## Toolkit Software Cdrom

This chapter previews the Real Options Analysis Toolkit software included on the CD-ROM. A few sample applications are provided, complete with step-by-step software illustrations. In addition, three technical appendixes provide all the function calls available to the user for direct access to the Real Options Analysis Toolkit software from Microsoft Excel, as well as a getting-started guide in using Crystal Ball's Monte Carlo simulation and stochastic optimization software package by Decisioneering, Inc.

## The Bollinger Squeeze

Besides the indicators described above, there is another special class of indicators, called oscillators, that fulfills an important role. Many of the oscillators, like Momentum and RSI, are very similar in formula and function. So they will not all be discussed here. Instead, only the most important and unique oscillators that are most applicable to foreign exchange trading will be covered. Oscillators excel at providing indications of overbought or oversold status during ranging markets. When an oscillator reading is above a certain overbought threshold during a trading range, it hints that upward momentum may soon be exhausted, and that an impending downward turn may occur. Conversely, when an oscillator reading is below a certain oversold threshold during a trading range, it hints that downward momentum may soon be exhausted, and that an impending upward turn may occur. As will be seen in Chapter 5, oscillators also excel at providing divergence signals, among other functions....

## Which Optimizer Is For

At the very least, you should have available an optimizer that is designed to make both brute force and user-guided optimization easy to carry out. Such an optimizer is already at hand if you use either TradeStation or Excalibur for system development tasks. On the other hand, if you develop your systems in Excel, Visual Basic, C+ +, or Delphi, you will have to create your own brute force optimizer. As demonstrated earlier, a brute force optimizer is simple to implement. For many problems, brute force or user-guided optimization is the best approach. Evolver product from Palisade Corporation is a good choice for Excel and Visual Basic users. If you develop systems in C+ + or Delphi, select the C+ + Genetic Optimizer from Scientific Consultant Services, Inc. A genetic optimizer is the Swiss Army knife of the optimizer world Even problems more efficiently solved using such other techniques as analytic optimization will yield, albeit more slowly, to a good genetic optimizer. Finally, if...

## Real Options Behind The Scenes

The first time-step has two nodes (S0u and S0d), while the second time-step has three nodes (S0u2, S0ud, and S0d2), and so on. Therefore, as we have seen previously, to obtain 1,000 time-steps, we need to calculate 1, 2, 3 . . . 1,001 nodes, which is equivalent to calculating 501,501 nodes. If we intend to perform 10,000 simulation trials on the options calculation, we will need approximately 5 X 109 nodal calculations, equivalent to 299 Excel spreadsheets or 4.6 GB of memory space. Definitely a daunting task, to say the least, and we clearly see here the need for using software to facilitate such calculations.2 One noteworthy item is that the tree below is something called a recombining tree, where at time-step 2, the middle node (S0ud) is the same as time-step 1's lower bifurcation of S0u and upper bifurcation of S0 d.

## Calculating Interest Rate Differentials and Following the Currency Pair Trends

The best way to use interest rate differentials for trading is by keeping track of one-month LIBOR rates or 10-year bond yields in Microsoft Excel. These rates are publicly available on web sites such as Bloomberg.com. Interest rate differentials are then calculated by subtracting the yield of the second currency in the pair from the yield of the first. It is important to make sure that interest rate differentials are calculated in the order in which they appear for the pair. For instance, the interest rate differentials in GBP USD should be the 10-year gilt rate minus the 10-year U.S. Treasury note rate. For euro data, use data from the German 10-year bond. Form a table that looks similar to the one shown in Table 10.1.

## Tracking Volatilities on Your

The next step is to start compiling a list of data for date, currency pair price, implied one-month volatility, and implied three-month volatility for the currency pairs you care about. The best way to generate this list is through a spreadsheet program such as Microsoft Excel, which makes graphing trends much easier. It might also be beneficial to find the difference between the one-month and three-month volatilities to look for large differentials or to calculate one-month volatility as a percentage of three-month volatility.

## Current State Of Systems

One of the most prevalent problems is that the antiquity of the major clearing and settlement systems in the back office has meant that they lack the flexibility to be able to handle the welter of new financial products emanating from the front office. Because of this, there is frequent recourse to manual intervention and Excel spreadsheets, with all the attendant potential for error that this entails.1

## Creating the Right Teams and Partnerships

The value of the right type of partnership is that each person brings a very unique skill set. For instance, Eric isn't the type of person who wants to sit down and create an Excel spreadsheet that shows whether the project is going to be profitable. But Sara loves doing that. Eric and Sara have a partner on their team who actually loves getting in his car and driving for two weeks across the state looking for deals. He enjoys talking to agents and driving to small towns and looking for 20-unit apartment buildings or strip centers. They have another person on their team who's excellent at marketing.

## Significance Test For r

Excel Application 11.2 Tests for normality suitable for the data in Figure 11.3 include the Lilliefors test and the Shapiro-Wilks test. However, these are not available within Excel (the interested reader should refer to a more advanced statistical text and specialist statistical software). A guide to normality can be obtained within Excel by using the statistical functions KURT(array) to calculate the kurtosis of each data set (shown in row 18 of Figure 11.3), and SKEW(array) to calculate the skewness of the data set. With a larger data set it might be possible to conduct a rough visual check of normality with Excel by constructing a bar chart (Section 8.4.3). However, using a statistical software package to produce normal plots would be better.

## Check Performance and Shadow

Experience shows that as trading investment systems are developed and tool sets change, regression bugs are quite common. Sometimes they occur because of poor control practices during the conversion from one tool set to another (e.g., conversion of Excel prototypes into C++ algorithms, or a switch of optimization engines).

## Perform Sensitivity Analysis

The sensitivity analysis above can be performed in MS Excel using the following process create an output table similar to the format above input the WACC and exit multiple ranges link the top left corner (shaded for presentation purposes) to the model output for enterprise value (cell containing 1,000 value in DCF model)

## Working with Random Entries

Note, however, that for a variable to be random it does not have to be normally distributed, as Figure 11.2 shows. This chart is simply created with the help of the random function in Excel. Figure 11.1, in turn, is created by taking a 10-period average of the random variable in Figure 11.2. More often than not, a random variable (like that in Figure 11.2) that is an average of another random variable (like However, a normal distribution can very well be both higher and narrower, or lower and fatter, than that in Figure 11.1. Most important, it has a single mode (one value that appears more frequently than the others) and is symmetrical, with equally as many observations to the left as to the right of the mean. For any normally distributed variable, it holds that 68.27 of all values lie within 1 standard deviation of the mean 95.46 of the values lie within 2 standard deviations and 99.73 of the values lie within 3 standard deviations of the mean. To calculate the standard deviation of...

## Exit Amd Abandonment Options

Figure 8.3 shows a down and out barrier abandonment option. This type of option means that a project will not be terminated immediately once it falls out of profitability. Instead, management sets a critical barrier assumption, and should the project's profitability level fall below this barrier, the project will be abandoned. The barrier may be set after accounting for project stickiness and any other operational issues. The analysis can be solved using the Real Options Analysis Toolkit software on the enclosed CD-ROM. In addition, basic barrier options can be solved in a binomial tree by adding in IF AND OR statements nested with the regular MAX functions in Excel.

## Appropriate View Technologies

Binary formats like PDF and Microsoft Excel's XLS can also be integrated with request-driven web MVC. Of course, they do not allow for interactive processing like form handling They are used only for certain views in an application, typically representing reports. In principal, they can be integrated with any web MVC framework, just like HTML generation code. The Spring Framework makes this particularly easy through offering prebuilt base classes for PDF and Excel generation The Spring Countries sample application illustrates this via its PDF and Excel reports.

## How Much Time is Needed

Trading signals can be generated manually via a simple PC spreadsheet. Just keep careful records and a trading log. You can also automate trading signals with products such as TradeStation or even Microsoft EXCEL. The Turtle trading course includes recommendations for both PC and Mac software packages.

## Emotional Intelligence

I am grateful to Dan Goleman for allowing me to reproduce some of his ideas here in application to market trading. His two books, Emotional Intelligence (1995) and The New Leaders (2002), are wide-ranging accounts of the importance of emotions in people's personal, social, and working lives. The title Emotional Intelligence was taken from an article published by Peter Salovey and John Mayer in 1990 which showed the relationship between emotions and rational thought. If we remember in the previous section the discussion of Phineas Gage who, despite being fully intelligent, had made disastrous choices in his business and personal life, and even obsessed endlessly over decisions so simple such as going to the shop or making an appointment. The relationship between bluntness or lack of emotions and inability to make rational decisions has now been well documented in many cases. Dr A. Damasio, a brain neurophysiolo-gist, has now published numerous research papers demonstrating that...

## Returns Analysis Internal Rate of Return

Internal rate of return (IRR) is the primary metric by which sponsors gauge the attractiveness of a potential LBO, as well as the performance of their existing investments. IRR measures the total return on a sponsor's equity investment, including any additional equity contributions made, or dividends received, during the investment horizon. It is defined as the discount rate that must be applied to the sponsor's cash outflows and inflows during the investment horizon in order to produce a net present value (NPV) of zero. Although the IRR calculation can be performed with a financial calculator or by using the IRR function in Microsoft Excel, it is important to understand the supporting math. Exhibit 4.4 displays the equation for calculating IRR, assuming a five-year investment horizon.

## LOOP 1 Program Business Rules Packages

Where the development team does need to convert a prototype from one implementation, say in Excel, to code over this stage, the development team must perform regression testing against Stage 1 results to ensure that errors are detected and fixed. Conversion often creates problems. Hopefully, experienced product team members will have designed prototypes in such a way so as to ease the conversion process. In the worst case, conversion becomes a reverse engineering project by programmers who may or may not understand the underlying finance theory or trading investment strategy. A common programming error at this point is changing data cleaning algorithms. A programmer may try to enhance these algorithms, failing to understand that a revision of input data requires a new backtest, a reversion to Stage 2. A complete regression test of the Stage 2 cleaning algorithms against the real-time implementations is critical. The development team should also discuss shutdown criteria due to bad, or...

## Property management checkpoints

Web-based property management software for all of our properties. Information is available to us 24 7 with a touch of a keystroke. So at anytime, we can find out about project accounting costs, budget monitoring, profit and loss reports, payable and receivable reports, tenant information, and maintenance status. Depending on the project, we create our own spreadsheets by using a program such as Microsoft Excel. For more expansive projects with multiple people and where sharing is involved, we'll use an online program such as Microsoft Groove.

## The Exchange Rate And Related Financial Data

The investigation is based on the London daily closing prices for the EUR USD exchange rate.3 In the absence of an indisputable theory of exchange rate determination, we assumed that the EUR USD exchange rate could be explained by that rate's recent evolution, volatility spillovers from other financial markets, and macro-economic and monetary policy expectations. With this in mind it seemed reasonable to include, as potential inputs, other leading traded exchange rates, the evolution of important stock and commodity prices, and, as a measure of macro-economic and monetary policy expectations, the evolution of the yield curve. The data retained is presented in Table 1.1 along with the relevant Datastream mnemonics, and can be reviewed in Sheet 1 of the DataAppendix.xls Excel spreadsheet.

## Taking advantage of automated trendlines

You can take some of the subjectivity out of trendline drawing by using a software package that constructs trendlines for you. All you have to do is choose your preferred time frame, and the software does the rest. Even Microsoft Excel includes this option in its charting function. (In Chapter 4, I explore how to use Excel for charting, but for now, I point out the way Excel determines a trendline for the data presented.) Figure 11-2 displays the same data as Figure 11-1, but instead of the hand-drawn approach, this trendline was generated by Excel. The placement of the line is a bit different than in Figure 11-1, but the trend is still clearly positive.

## Use And Abuse Ofthe Computer

Computers are not a substitute for thinking. They excel in performing the same tedious task, over and over again, quickly and accurately provided, of course, that correct information was entered. But even though technology has not yet reached the stage depicted in Star Wars, the computer is the only practical tool for evaluating trading ideas. This section will consider both good and bad ways to approach a computer problem, none of which can be credited to or blamed on the machine. As a powerful tool, a computer can't be beat many of the systems, advancements, and refinements presented in this book could not have been considered without it.

## LOOP 1 Test against Black Box Results from Stage

QA regression tests take known data (i.e., data used during K V 2.4) and known model inputs for calculations. The financial engineer leads QA regression testing and makes sure the tests are done to specification, mapping inputs to outputs. For example, the financial engineers may run 100 products and five years of data through the production software to verify that all inputs lead to all the correct outputs. If the outputs are not identical, the team will have to hunt down the differences. A normal source of difference is in rounding algorithms. Rounding algorithms in C++ of COTS components may not match the rounding algorithms in Excel or MATLAB. All differences in rounding algorithms, interpolation algorithms, and precision tolerances in optimization should be investigated with a documented conclusion. The financial engineers should keep a list of known differences and their causes for future discussion with the product team or top management. Depending on those causes, they may or...

## Pork Bellies To Pork Bellies Systems To Systems

Earlier I mentioned a few important ratios that are used to evaluate investments such as mutual funds and managed futures funds. These formulas are also an excellent way for you to judge yourself as an investment. Are you a good deal If you compared yourself with a Nasdaq 100 index fund, how would you rate At the end of your second year of fulltime trading, will you be an attractive property for a complete stranger to invest in This may sound silly at first, but if you aren't, you may have some tough investment decisions to make. Should you continue to invest in yourself Each window is calculated by rolling one month's statistics forward, dropping the last month's off, and then recalculating, which can easily be programmed on Microsoft Excel or other spreadsheet software. A year of windows breaks out as shown in Table 9-6. Besides being an excellent source of data for your advanced analysis, the report reveals many of your strengths and weaknesses. For example, I was working with a...

## The Confidence Chickenand Egg Question

One of the most strikingly evident traits among all the Market Wizards is their high level of confidence. This leads to the question Are they confident because they have done so well, or is their success a consequence of their confidence Of course, it would hardly be surprising that anyone who has done as extraordinarily well as the traders in this book would be confident. But the more interviews f do with Market Wizard types, the more convinced I become that confidence is an inherent trait shared by these traders, and is as much a contributing factor to their success as a consequence of it. To cite only a few of the many possible examples When Watson was asked what gave him the confidence to pursue a career in money management when he had no prior success picking stocks, he replied, Once I decide 1 am going to do something, I become determined to succeed, regardless of the obstacles. If I didn't have that attitude, I never would have made it.' Masters, who launched his fund when he...

## How To Use The Pivots Program

It calculates the low and high, pivot point, and resistance and support points for the current trading session, based on the open, high, low, and close of the preceding session. All you have to do is input the open, high, low, and close (no decimal points) and click on any open space in the spreadsheet. And, there you have it walaa all pivot resistance support points for the next trading session will appear before your very eyes. It is important to track the average range, as this information is not available anywhere else. Going into a trading session, it is important to know this average.

## The Retirement Annuity

Let's first see how this spreadsheet was constructed. To view the formulas of all cells in an Excel spreadsheet, choose Preferences under the Tools menu, and select the box Formulas in the View tab. The formula view of Spreadsheet 18.1 is also shown on the next page (numbers are user inputs).

## Doing Your Accounting Manually

When you own several properties, consider using a computer with a spreadsheet or general accounting software program. Spreadsheet programs, such as Microsoft Excel, can handle a few rental properties. Somewhat better are the general business accounting packages, such as the entry-level Quicken, and the more advanced QuickBooks or Peachtree Accounting. These programs can handle and streamline all the basic accounting requirements of managing a handful of rental properties.

## The Importance of Setting Goals

Doctor Kiev stresses that exceptional performance requires setting goals that are outside a trader's comfort zone. Thus, the trader seeking to excel needs to continually redefine goals so that they are always a stretch. Traders also need to monitor their performance to make sure they are on track toward reaching their goals and to diagnose what is holding them back if they are not.

## Building Portfolios of Managers The Current Common Sense

It's no different from how excellent active managers pick stocks they use a tremendous amount of research into fundamentals, and at the end of the day, they make a judgment call informed by their trained instincts. In Those pesky expected alphas, below, we touch on other issues related to forecasting alphas for managers. information ratio times the square root of the number of years of data. Every regression software package, including Microsoft Excel , provides this t-statistic automatically whenever a regression is conducted. By using a regression alpha, we eliminate any accidental market return effects that might otherwise distort the manager's actual non-market-related returns. It is pure active return, or alpha.

## Past Is Prolog Sampling with Replacement 229 Past Is Prolog Sampling with Replacement

An example will illustrate the idea behind sampling with replacement (see Table 8.1). Using the numbers from 1 to 10 as our original sample, we calculate its average (5.5) and standard deviation (3.03). We then use the sampling-with-replacement algorithm in Microsoft Excel 5.0 to generate 11 additional samples. If you study the samples for a minute, you will see that the same value often occurs more than once. The values are being drawn at random from the original sample, so that each of the 11 samples is different. At the same time, we retain the signature of the original data set, as measured by the difference between the highest and lowest value.

## Data Scrambling All the Synthetic Data Youll Ever Need

Here we used the 11 29 95 close of 608.05 as reference, and generated a new sequence of bars using the sampling function in Microsoft Excel . The new sequence was 4, 5, 8, 1, 3, 10, 10, 8, 9, 1. Therefore, starting with the previous close of 608.05, we put in the fourth bar of the original data, then the fifth bar, and the eighth bar, and so on.

## Research assistantsassociates

To excel initially, research assistants and associates must work hard, learn quickly, and become whizzes at Microsoft Excel and Word. Especially important to research associates are good writing skills, as analysts often hand-off a significant portion of the writing and editing of research reports to the associate. Early on, the biggest mistake a research assistant or associate can make is to mess up the financial models and generally lose sight of the details.

## The Lure Of Active Management

The Excel model Performance Attribution that is available on the book's website is built on the example that appears in section 20.2. The model allows you to specify different allocations and to analyze the contribution sectors and weightings for different performances. The Excel model Performance Attribution that is available on the book's website is built on the example that appears in section 20.2. The model allows you to specify different allocations and to analyze the contribution sectors and weightings for different performances.

## Value at Risk Implications

Let z be the standard normal variable, N(0,1), and let the return R be normally distributed with mean m and standard deviation o, R N(m, o2). Let a' 0.025 e 0,1 , where the associated quantile Za is defined by the following probability statement Pr(z Za) a'. Equation (2.1.3) in Chapter 2 defined value at risk as VaR(a') -Ra * K, where Ra (m + Zao) and K denotes the capital invested. Note that a' is a low percent quantiles of a probability distribution. The quantiles Za' involve looking up the normal tables backward. With the wide availability of the Microsoft Excel program, the inverse CDF is simply one of the available functions, so the table lookup is no longer needed. Table 9.2.1 reports the quantiles from the inverse of the CDF of unit normal (NORMINV) in the column entitled quantiles. The last row of the table suggests an Excel command. For example, a' 0.01 level estimate in Table 9.2.1 based on Excel is Za' -2.326347 compared to the more accurate Table 9.2.1 Normal Quantiles...

## An Elementary Description of Simulations

VaR can be computed by a careful listing of what can happen to (1) prices, (2) quantities bought and sold, (3) financial instruments bought and sold, (4) the firm itself through deaths of key personnel, mergers and acquisitions, natural disasters, terrorism, litigation, corruption and other unforeseen events. We recommend that these lists should be quantitative and be accompanied by corresponding probabilities, mostly based on past data. Of course, in the absence of data, expert opinions and guesses can be used. If the probabilities are conditional probabilities (given the values of related items), it is nowadays possible to create the unconditional (also known as marginal) density by using Gibbs sampling computer algorithms. Casella and George (1992) give an excellent introduction to the idea behind Gibbs sampler. It is often best to use marginal densities and let the simulation model represent the interactions. Depending on the complexity of a portfolio, it is clearly possible to...

## Learn the Local Rental Market Structure The rental market is one

There are times of the year for most neighborhoods when there are more properties for sale as a general rule. For South Florida, this would be the summer months, when there are fewer buyers in town, and many owners who have left for cooler climates for the hot (and hurricane prone) months have put their properties on the market. In addition, there are times of the year for some areas when housing starts and sales drop off, often the cold blistery months in the northern parts of the country when construction slows due to the weather. For those reasons, avoid applying statistics of those different areas to your own backyard but do learn what is going on and why different areas excel or fall below the standards of your comfort zone.

## Consolidation Within The Industry Is Bigger Really Better

In the early years of the twenty-first century, Felcor acquired Bristol Hotels, New Plan Realty acquired Excel Realty, and Bradley Realty bought Mid-America Properties, before itself selling out to Heritage Property Trust, a private REIT (which went public soon thereafter). Equity Residential bought Merry Land, and Reckson Associates and Tower Realty combined. ProLogis Trust bought the assets of Meridian Industrial Trust, Duke Realty and Weeks Corp. merged, as did Health Care Property and American Health Properties. Pan Pacific Retail bought neighborhood shopping center Western Investment, and Archstone acquired Charles Smith Residential, combining two strong apartment REITs. Finally, not content with an acquisition of Cornerstone Properties, Equity Office struck again this time acquiring the highly regarded West Coast office REIT, Spieker Properties, in a deal valued at approximately 7.2 billion and boosting Equity Office's equity market cap to 14.2 billion.

## Step IVb Complete Pro Forma Income Statement from EBIT to Net Income

23At this point, a circular reference centering on interest expense has been created in the model. Interest expense is used to calculate net income and determine cash available for debt repayment and ending debt balances, which, in turn, are used to calculate interest expense. The spreadsheet must be set up to perform the circular calculation (in Microsoft Excel) by selecting Tools, Options, clicking on the Calculation tab, checking the box next to Iteration, and setting the Maximum iterations field to 1000 (see Exhibit 3.30).

## Notes on System Testing

That said, when using the points-only tests on futures, there is no such compounding. Each trade assumes a one-lot size, which means the gains (if there are any) do not get compounded in the form of larger positions (or smaller ones, if declining equity follows a series of losing trades) when a new trade signal is generated. If the system is doing very well, it is understating its potential equity if no compounding of gains is built into the trading program. Clearly, given some of the very high triple-digit returns from some of the system tests using no compounding, and given the stable equity plots, even a small degree of compounding of gains would lead to dramatic improvements in equity growth. Again, MetaStock Professional limits this ability, although it is certainly possible to produce a simulated compounded equity plot with the data using Microsoft Excel, something I do not undertake here.

## Types of Hedge Fund Investors

The arithmetic average return after management fees and incentive fees imposed by the fund of funds manager is 0.91 percent. In Excel, use AVERAGE(1.20 , 1.02 , .53 ) . The geometric average return is 0.92 percent. To calculate the geometric average, simplify the following expression

## Concluding Remarks

Overall, for the periods and the data series concerned, the results of PCA outperformed all the other methodologies in all cases of missing observations and consequently in the calculation of HDDs. The only drawback of PCA compared with the second most accurate method, the fallback method, is that PCA requires more correlated weather temperature clean data. Nevertheless, if the necessary data are available, PCA should be preferred in replacing missing temperature observations. More generally, PCA provides a very efficient and simple method for filling missing data in the presence of a correlated system of variables. As has been shown, it is also easy to implement, as this can be done in Excel.

## Real Options Model

A similar approach is to use the Roll-Geske-Whaley (RGW) approximation. Note that these approximation models cannot be readily or easily solved within an Excel environment but instead require some programming scripts or the use of software. The Real Options Analysis Toolkit software CD-ROM has these American approximation models as well as the ability to solve up to 5,000 time-steps in the binomial approach. 2. Note that these approximation models cannot be readily or easily solved within an Excel environment but instead require some programming scripts or the use of software. Be aware that closed-form American option approximation models can only provide benchmark values for an expansion option. 4. The model is shown in Appendix 8C. Note that these approximation models cannot be readily or easily solved within an Excel environment but instead require some programming scripts or the use of software.

## Appendix 7g Applying Monte Carlo Simulation To Solve Real Options

The Excel spreadsheet is located in the Examples folder under the name Simulated Options Model. Note that the example spreadsheet requires that Crystal Ball's simulation software be installed to run properly. To obtain similar results shown above, simply open the spreadsheet and hit the RUN icon. Finally, note that because Monte Carlo simulation is by definition a random selection of values from predefined distributions, the results may not match exactly those seen in the examples. Finally, to obtain similar results in the charts, the range of results shown is set from 0.01 to infinity in Crystal Ball 3. The Excel spreadsheet is located in the Examples folder under the name Simulating Options Analysis. Note that the example spreadsheet requires that Crystal Ball's simulation software be installed to run properly. To obtain similar results shown above, simply open the spreadsheet and hit the RUN icon. Finally, note that because Monte Carlo simulation is by definition a random selection...

## The Dynamic Breakout System

In this chapter we apply that system (version lb) to the same 16 markets as in Part 2, to examine the possibility of increasing its performance by adding a few stops and exits in accordance with our findings regarding John Sweeney's MAE MFE methods. The time period covered is from January 1980 to October 1999. Another reason I chose to work with this system is simply that the original system is closer in resemblance to the standard deviation breakout system that follows. The export function from TradeStation into Excel is essentially the same for the DBS system as for the directional slope system. When you are done exporting and calculating in Excel, you can put together a set of tables like Tables 10.6 and 10.7 for all of the individual markets, and Tables 10.8 and 10.9 with a composite of the most important measurements. In Table 10.10, finally we summarize the differences for each market. Tabic 10.6 shows that with the

## Introduction to portfolio construction

This is cheap and smart software that for as little as 349 can do whatever you need in the way of portfolio testing. It allows you to import system report data from TradeStation in Excel format, even if you need to do it for every single stock or future in the portfolio, (and this is quite time-demanding.) It performs equity line crossovers, Monte Carlo analysis, trade dependency and portfolio analysis.

## The Standard Deviation Breakout System

When traded on 16 different markets, using the RAD contract, on data from January 1980 to October 1992, it traded profitably on all 16 markets. The 16 markets traded were crude oil, T-bonds, T-bills, rough rice, Nikkei index, natural gas, live cattle, lumber, coffee, copper, gold, dollar index, Japanese yen, D mark (as a proxy for the Euro), cotton, and wheat. The export function from TradeStation into Excel is essentially the same for the SDB system as for the directional slope system. The data for the period November 1992 to October 1999 were saved for some out-of-sample testing in later sections. Figure 10.2 shows that the open equity for the average trade is exceptionally smooth all the way up to over 150 bars, and that we can expect the average trade to increase its open profit with about 0.18 per bar. We will make use of this finding later, when we put together a trailing stop. The dotted line denotes the number of bars from which point on there are fewer than 20 open trades....

## High Probability ETF Trading Addon Modules

Use this module to scan ETFs nightly after the market closes for potential setups for the upcoming trading day. When finished, you will receive a complete report which includes suggested entry and exit levels with each signal. You can export this list to an Excel file for easier sort-ing.

## Estimating Beta Coefficients

The spreadsheet Betas, which you will find on the Online Learning Center (www.mhhe.com bkm), contains 60 months' returns for 10 individual stocks. Returns are calculated over the five years ending in December 2000. The spreadsheet also contains returns for S&P 500 Index and the observed risk-free rates as measured by the one-year Treasury bill. With this data, monthly excess returns for the individual securities and the market as measured by the S&P 500 Index can be used with the regression module in Excel. The spreadsheet also contains returns on an equally weighted portfolio of the individual securities. The regression module is available under Tools Data Analysis. The dependent variable is the security excess return. The independent variable is the market excess return.

## Application To International Equities

In this section we describe an application of the cointegration tools and techniques described above to data from those international equities which comprised the STOXX 50 index as of 4 July 2002. We describe this analysis with reference to the accompanying Excel workbook named equity coint.xls on the CD-Rom.