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.