CS 100 - Computer Literacy, Spring 2005

Lab 13

Objectives

Learn how to use Microsoft Excel
Learn how to do simple calculations in Excel
Learn the difference between absolute and relative cell references
Learn precision and formatting in Excel
Learn how to create graphs in Excel
Learn how to add trendlines in Excel

What is Microsoft Excel?

Microsoft Excel (or just plain old Excel) is a spreadsheet application. Spreadsheets are ways to organize data by grouping it into rows and columns. They are used for many jobs in the real world, ranging from Accounting to Insurance to Chemistry. Excel is a good program for these types of applications because it is an easy program to learn and use. Excel is limited, however, and more in-depth data analysis is done through more sophisticated programs.

Vocabulary

Cell – the small boxes where data exists in a spreadsheet
Function – just like in math, an operation performed on variables or constants

The Assignment

The file DisneyPrices.txt is a file that contains dates, prices, and volumes for Disney stock trading. This file was created by using the program ExcelCode.py on the web page at http://finance.yahoo.com/q/hp?s=DIS. If you look at this file you will notice that there is a tab of space between each element. This is because Excel can read in text files that are formatted in special ways. When a text file has the information in it separated by tabs. It is called a Tab Delimited file. Another common delimited file is comma delimited. It is also know as a csv file because its extension is .csv. You will see other delimiting types as we move into the lab.

EXCEL

Start Excel.
Select File > Open
In the file chooser window, There is a field "files of type". Change that to "Text Files(*.prn;*.txt;*.csv)"
You should now be able to choose text files, so select and open "DisneyPrices.txt" (note this should be whatever you called the file. I just happened to call mine DisneyPrices.
You will now see the text import wizard. This allows you to open file types and formats that are not necessarily built into excel. In our case we will select "Delimited" and click "Next".
A Delimited file can have many things separating its fields, commas, tabs, etc. The file we generated happens to use tabs. Go back and review the code if you do not believe this. You should see many "\t"s.
Select the Tab box and click Next.
You should be on step three. At this point click "Finish"
You should now see something that looks similar to the following.

Note that the first column in the spreadsheet is labeled A, and the first row is labeled 1. Spreadsheets use a coordinate system to access information. Each cell in the spreadsheet is identified by the letter-number combination of its location. Ergo, information in Cell A1 is whatever is the most recent date that Disney stock was traded on.

Looks Matter: Formatting

The first thing we want to do is format our cells to make the information easier to understand and more standardized.

Formatting the Date.

Procedure:

Select all of column A by clicking on the "A" at the top of the Column
Goto the menu at the top of the screen. Find "Format". Select it and the select "Cells"
We are working with dates here so select the "Date" category.
Under this option are many varieties of date format. Select the one that is "03/14/01"
Click OK

You should now see all of the dates in the form xx/xx/xx.

ForYouToDo: Formatting the numerical data.

If you will recall, our text file had all data out to two decimal places. However, our Excel file is only showing the minimum number of places needed. i.e. 23.00 has become just 23. You may have also noticed that some of the cells in column F show up as xxxxxxx instead of as numbers. This also can be fixed using formatting.

Following the procedure above, format columns B - E to have two decimal places.
Also following the procedure above, format column F, to be a number with zero decimal places.

HINT: You may want to look at the number category for both of these.

The Selection Process: Adding or Removing Rows or Columns.

There is a column of data that to be honest, we are not sure we want. That is column G. To delete that column:

Select column G
Goto the menu at the top of the screen and select "Edit".
Now select "Delete"

The next thing we begin to notice is that we have forgotten what all of the various data in the columns is. We can pick out the dates and volume easily enough but the open and closing and high and low prices are lost to us. To remedy this, we want to add a header row. To add a header row:

Select the first row. (Select the "1" on the left side of the screen.)
Goto the menu and select "Insert"
Under that menu, select "Rows"

You should now have a blank row at the top of the work book. Let us fill it in now. Either look at the code in ExcelCode.py or goto Yahoo's historical stock prices page at http://finance.yahoo.com/q/hp?s=DIS and pick out what the names of each column should be. When you have what each column should be, then select each cell in the header row and fill it in.

The Getting Into Wavy Line-like Formations Competition: Graphing

Since we are dealing with stock data and we are used to seeing that data in charts and graphs, let us make a graph.

Select all of the data in column E except the first row. (Click on E2 and hold it and drag it down to the bottom of the E data.)
Goto the menu and select "Insert".
Choose the XY(Scatter) Chart Type.
Under the Subchart Type, choose the one that is just points. (Scatter. Compares pairs of Values.)
Click Next
Click Next
You should now be at a screen that will allow you to put in a chart title and X and Y axis labels. The chart title should be something about Disney Stock Prices. For the X and Y axis, X should be "Day Number" and Y should be "Price".
Click Next
This is the final screen. We can put a chart into the graph we are working on. However, for neatness lets put it in its own workbook. Select "As New Sheet" and click Finish.

Oh Dear, We're backwards: Sorting

Sometimes, Data is not in the order we want it. In this case, the stock prices are backwards (the most recent close is on the left of the graph, the oldest close is on the right). Let us fix that.

In the upper left hand corner of the spreadsheet is a graybox with no label on it. If you click on it, you will select the whole sheet. (It is above row 1 label and to the left of column A label)
Select "Data" from the menu
Select "Sort..."
Choose Sort by Date
Choose to make the Sort Ascending
Click OK

Now all of the data will have moved around. The earliest date will be at the top of the screen and the most recent at the bottom. A benefit of doing the sort on the whole graph(instead of just selecting a part) is that it keeps the related terms together.

There is more to graphing than just making a pretty chart though. Graphs in Excel allow us to do many things. One thing we want is to see where this stock is going. To some mild degree, that can be accomplished with a trend line. Let us make one.

Click on the tab that takes you to your chart
Click on one of the data points in the chart
Goto the menu and select "Chart"
Select "Add Trendline"
Choose the Polynomial Trendline
Change the order of the Polynomial Trendline to 6. Note that six is the highest order polynomial trendline that Excel can do. Generally speaking with polynomial trendlines, the higher the order of terms, the more accurate the trendline.

Oh No, Not Maaath: Relative Formulae and Simple Functions

(Subtitle: wait a sec, this is a CS class ... )
Head back to the data page in Excel and let us work with two blank columns. Perhaps we want to determine the maximum stock motions in one day. First, we will do the motion above the close then, the motion below the close.

In the first cell of column G, place the label Motion Above.
We can calculate this as |Close - Max|
In terms of Excel, we are going to have to be a more specific. Excel will not allow us (many times) to flat out write mathematical expression. In this case, we will need abs() to calculate absolute value. In cell G2 write =ABS(E2-C2).
This will calculate the magnitude of the difference between the close and the high for that day.

Next let us do the motion below the close (this will be up to you). Label Column H similarly to Column G and then figure out the formula for motion below the close (It is similar to that for motion above the close).

Now Do The Math Again, 60 More Times: Copying

One of the powers of Excel is that once you write a formula, you really do not need to write it again and again to use it on new data. You can just copy and paste it and the proper cells will be updated.

Select cells G2 and H2
Select from the menu "Edit"
Now select "Copy"
Now, select all of the cells that you want to copy these formulae into. In this case, select all of the cells in the two columns down to the point where the original data ends.
Select from the menu "Edit"
Now select "Paste"

All of the selected cells should now be filled with data.

Rounding Third Base, I've Seen This On TV Right?: Error Bars

Here we will add error bars to our graph. Typically speaking, error bars are not used as we will use them here. Instead, they are used as a representation of percent error, standard deviation, standard error, or a number of other things. This is a decent use of them though. Here, we want to show the Above and Below Values that we just calculated so that we can see how much the stock is fluctuating on a daily basis.

Go back to your graph page.
Select one of the data points.
Select "Format"
Now select "Selected Data Series..."
Select Y error bars
Select Both
Select Custom
First we will choose the values above. By the custom selection there is a box that is labeled with a +. Beside that, there is a button that will allow you to select a range of values. Press the button.
Select column G.
Press Enter.
Beside Custom there is also an area labeled -. It too has a button that will allow you to select a range. Press it.
Select column H
Click OK

You should now be able to see some small lines with bars on them above and below each of your data points.

Did I Win? Do I Get To Goto Vegas Tomorrow? More Math and Absolute Formulae.

Formulae can be used for other things and we can reference single locations with a formula. For starters we want to know what the percent change is each day. The formula for this is the amount change between the current day and the previous day(E3 - E2) divided by the previous day's close(E2). Type this formula into I and copy paste it down to calculate the percent change for the rest of the data. After you have calculated the percent change in Column I, then use your knowledge of cell formatting to make the column display a percent to 2 decimal places.

Absolute formulae. There are times in Excel when you do not want everything to be relative. In other words, you do not want to copy L1 + D1 into all the other cells in its column and have them become L2 + D2, L3 + D3, etc. To overcome this, Excel has an absolute cell reference method. To use it, put a $ before each element in the cell. For example $L$1. That would be an absolute cell reference. If you copied some formula using that absolute reference, it will always refer to the same cell. In the formula above, if we had $L$1 + D1 and copied that down a column, the other cells would appear as $L$1 + D2 , $L$1 + D3, etc.

Awww Fooey, Let's All Go Home: A Different Kind of Formatting and The End.

Excel is not always the prettiest thing to look at. There are other ways we can make our data look better. One of those ways is to use Conditional Formatting. Let us do two conditional formats on column I. Follow the procedure below for the first one.

Select Cells I2 down to the bottom of the data in this column.
Goto the top menu and select Format.
Select "Conditional Formatting ... "
What we want is for our positive percent change to be in BOLD and for our negative percent changes to be BOLD and Red .
You should see some areas that say something like "Cell value" "is less than" "0". Put in conditions into these cells to make the effect any number less than 0. Also notice the "Add" button, this may be useful later.
Click the Format Button. Here you can choose font style, color, size, and a few other things. Use these options to make these cells Bold and Red.

On your own, go back and add a second condition to the I column of cells. Setup this condition to apply to anything greater than 0. Then set the format so that these cells appear as bold and black.

Lab 11: Due Monday 4/25/2005 by 12:00 noon

Save your Excel file as StockInfo.xls. Email your file to the CS 100 Email Account i100@cs.uic.edu. You may also post this file in your public_html directory on your incarus account with a link to it on your homepage. Clicking on this link will cause the page to either be displayed for downloaded to the local machine (this depends on the settings of the web browser).

This lab was originally written for Georgia Tech's CS 1315 and the original write-up can be found as Lab 5: Excel Stuff.