Microsoft Excel Tutorial Series: The basic’s data entry and navigation

If you have not already read the brief introduction to Excel 2007 I would advise you do so before continuing on with this tutorial series. You may be asking yourself what is the point using Excel as an analysis platform when there are products such as Wealth Lab and TradeStation available. I think that it is important that every trader be able to understand the underlying concepts of the indicators they are using and be able to analyse data. By undertaking this tutorial series you will also get the chance to learn by doing, which helps re-enforces concepts much better than if you were to just passively read the articles.

When you first start Microsoft Excel 2007, you will be presented with a screen similar to the one shown below. The key areas and terminology are also highlighted.

1. The active cell has a square border surrounding it. A cell can be activated by single clicking.
2. The formula bar allows formulas or data can be entered in this area. If you wish to insert a formula, the first character in this bar must be the equals sign.
3. The cell format controls will probably be the most extensively used control. The format options include general (no specific format), number, currency, accounting, short date, long date, time, percentage, fraction, scientific and text.
4. The expand formula button is useful when entering complex formulas as it allows you to see the whole formula without having to scroll.
5. This button allows for the recording of a macro and is an easy way to tell if a macro is currently being recorded.
6. The active sheet is the highlighted sheet at the bottom of the screen. A sheet can be made active by simply single clicking on it. To rename the sheet you simply double click on it.
7. Inactive sheets are every other sheet that resides in a workbook that are not currently active. These have a gray appearance.
8. You can add new sheets to the workbook by clicking this button.
9. The status bar gives you instant feedback about the current operation.
10. Page layout allows you to change the layout of pages on screen. You have three options normal, page layout, or page break preview.
11. Page zoom allows you to dynamically zoom in and out of the current workspace.

This has been a very quick introduction to the main navigational components and terminology used in Excel.

Calculating the average closing stock price

In our new spreadsheet the first thing we will do is name the active sheet by double clicking on it, we will call the sheet “StockInfo”.

Click to activate cell A1 and enter the text “Day”.

Click in Cell B2 to activate it and enter the text “Adjusted Close”. You will need to adjust the cell to accommodate the text. The process is shown in the image below.

Click in cell A8 to activate it and enter the text “Average Closing Price”. You will have to follow the procedure outlined above to increase the size of the cell to accommodate the text.

Under the day column, starting in A2 enter each day of the week in a new cell.

Enter the following adjusted close prices in the adjusted close column, starting at B2 enter the closing price for each day.

Notice the closing prices do not have a preceding dollar sign. Before we move on, we need to change the format of these adjusted close prices from general. To do this select all of the adjusted close prices and click on the dollar sign in the cell formatting controls box.

As I am sure you all know a simple average is merely the sum of all of the individual observations divided by the number of observations. In this example it would be

MondayClose + TuesdayClose + WednesdayClose + ThursdayClose + FridayClose / 5

Excel makes this process simple with the use of formulas. Before we enter the formula, we will set the format of the cell B8 to currency as described above.

To indicate to Excel that we are entering a formula the first character you enter must be an equal’s sign. So activate cell B8 and enter the = sign. As we can see from the formula above, we need to add each day’s closing price and divide by 5. Enter the formula as shown below.

If everything has been entered correctly, you should have an average adjusted close price of $10.17.

Trainee Trader Homework

That’s right you have homework to do, first go through the worked example above.

1. Enter this data above into excel
2. Adjust the size of any columns so all the text is visible
3. Format the adjusted close price as currency.
4. Calculate week ones average adjusted closing price.
5. Calculate week twos average adjusted closing price.
6. Calculate the combined week one and two average adjusted closing price.

I have attached the answers in an excel spreadsheet download this after you have worked through the problem. If you have any questions at all please add them in the comments field below.

TraineeTraderTutorialOneHomeworkAns.xls

About the author

Mark - Is a 28 year old form Australia. He has a Bachelor in Economics and a Major in CS. He is currently working towards his CFP. His interests include algorithmic trading systems, artificial intelligence, game theory and poker.