Saint Joseph Logo

Saint Joseph - Hessen Cassel

Fort Wayne, Indiana


Stock Market Project

Stock Market Project

This webpage will be your guide to your Stock Market Project.
It will be broken up into several parts, each due on certain days.
Remember that you can use Open Office Calc or Microsoft Excel (online or installed).
You can even go from one to the other.
If you have a different version, or a different program, it should still be compatible.
Other programs you can use include (this means they include spreadsheets):
Microsoft Works, Google Sheets, Apple’s Numbers, LibreOffice.
The Record Our Stock:
Honda  (HMC)
Date Price
11-13 $32.54
 11-21 $33.12
 11-28  33.40
 11-30 33.34
 12-5  33.20
 12-7  33.50
 12-12  33.52
 12-14  33.66
 12-19  33;68
12-21 34.18
12-28 Luke
 1-2 Drake
 1-5 Layla
1-9 Connor
 1-15 Hannah
 1-20 Nelson
 1-26 Hunter
 1-30 Justin
 2-1 EC
 2-6 EC
 2-13  EC
 2-18  EC
 2-23  EC
 2-28  EC

Intro  (due 12-4):

Type your name in cell A1.  Decorate it!

Research several companies.
Hopefully they will perform well (but that part is just for fun!).  Hopefully they will be of interest to you.
In cells A3:A7, type the names of the five companies that you have chosen to track for this project.
Part I  –  Due December 11 at end of class.
We need another blank line, so insert a row above Row 3.
Now we will use the new Row 3 for column headers.
Type the following column headers:
A3: Company
B3: Symbol
C3: Exchange
D3: Date purchased
E3: Time purchased
F3: Stock price
G3:  Number of shares
H3:  Total investment
At the bottom of the section, after the last company (which should be cell A9), type the heading Totals.
Format all headers thusly:  bold, font size 15
How to fill in the data:
Look up and fill in column B with the correct company symbols.
Ignore column C for this week.
Fill in columns D and E with the data that reveal when you are doing this.  Different companies may have different data, depending on when you are doing this.
Look up the stock price.  If you are doing this after the market closes, use the Closing Price.
Format the column of prices as currency (this means with $ and 2 decimal places).
In column G, type the number of shares you wish to buy.  You can, and should, change this often, to get to your goal.
In column H, type a formula to calculate the total investment you are making in that company.
At the bottom of column H, calculate the total that you have spent.
Format this cell as bold and green.
The total must be no higher that $10,000.
You have breathing room of $10.
EC option:  Spend exactly $10,000.
Play around with the # of shares (column G) to achieve the desired total.
Part II – Due December 19
Under the Exchange heading, fill in the exchange on which each of your stocks is traded.
Now it is time to update our stock portfolios.
We will do this 3 times, approximately every week.
Each week, we will also add a new column.
These new columns will be explained both here and in class.
For today:
  1. Skip two rows.
  2. Reproduce all of the column headings and formulas from the previous session.
    The exceptions are Symbol, Exchange and Shares.
    Why?  Because these data do not change.
  3. At some point before the due date of December 19, look up the current price of each of your stocks.
    Fill in the date and time that you are doing this.
  4. Place this price under the appropriate heading.
  5. Under Total Investment, create a formula to calculate the stock’s latest value.
    This is similar to what we did last week.
    You will use the cells with the new stock price, and the same cells as last time with the number of shares.
  6. Calculate your new portfolio total.
  7. Change the format of this cell so that negative numbers appear in red.
    New Column:
  8. Add a column and label it $ Change.
  9. Write a formula to determine how much your stock price changed since the last time.
  10. Total this new column.
  11. Cause negative results to appear in red.
  12. To repeat, numbers 8-11 are new for this week.

Part 3

Due: January 11

Session info:
Same as Session 2.  Create another session with new date, time, share prices, Total Investment, and $ Change.
New Column: After the column $ Change, add another column.  Head it $Change since start.
For the data, create a formula to calculate how much your stock’s value has changed since the first session.  As usual, cause negative numbers to appear in red.


Part IV

Session info:
Same as other sessions.  Create another session with new date, time, share prices, Total Investment, $ Change, and $ Change Since Start.
New Column: After the column $ Change Since Start, add another column.  Head it %Change since start.
For the data, create a formula to calculate how much your stock’s price has changed since the first session as a percentage.  As usual, cause negative numbers to appear in red.
Total the percentage to refer to the change in your total investment.

Due: Jan 18


EC (3 pts added to lowest score):  see HMC table – 1st four to deliver 1 answer!

Part V

As you saw in class, choose 2 of the 3 graphs to analyze your portfolio.
These must be completed on Calc or Desktop Excel (home or school).

First, go to your OneDrive in your account.  Then, click on your SMP file. Then click the download link above. Choose a proper location. Then open in one of the above programs.

Options and requirements:

  1. Use a pie chart to show the percentage of each company in your portfolio.  Use the Number of Shares column as your data.
    1. Create a new worksheet.
    2. Label the worksheet Holdings.
    3. Color the label yellow.
    4. Go to the worksheet with your SMP.
    5. Select your 5 companies.
      1. Choose Insert -> Chart.
      2. Select Pie.
      3. Select the 3D option.
      4. Choose Exploded Pie Chart.
    6. Click Next.
    7. Right-click on a slice.
      1. Choose Area…
      2. Under the fill tab, choose bitmap.
      3. Select a bitmap fill that starts with an S.
    8. Click OK.
    9. Change the color of your text to better contrast with your new background.
    10. Double-click a slice.
    11. Click Borders.
      1. Change the color of a slice’s border to green ($)!
      2. Change the width to .07.
      3. Labels:  Right-click on a slice, choose Format Data labels.
      4. Rotate labels 340 degrees.
    12. Ensure all options are checked, to show amount and percentage.
  2. Use a bar graph to show the percentage of change for each company throughout your project. Use the Percent of Change column from session 4 for your data.
    1. Select the 6 cells that make up your data for the category Percent Change Since Start (in Session 4).
    2. Include the top header row as a label.
    3. Go to Insert -> Chart.
      1. In the wizard, select Bar Graph (if asked).
      2. In #2, ensure you have data series in columns, and first row as label selected.
      3. #3, accept defaults.
      4. #4, type info to serve as title, and axis labels.
    4. Click Finish.
    5. Double-check the accuracy of your chart.
    6. Double-click a blank area inside your chart.
    7. Click the Borders tab.
      1. Choose Fine Dash style.
      2. Color=salmon
      3. Width=.04
    8. Double-click a bar.
      1. Choose the area tab.
      2. Choose Blue3 as color of the bars.
    9. Double-click a number on the Y-axis.
    10. Changes:
      1. Remove the Automatic option for Minimum.
      2. Change it to what you think works best for your data.
      3. Click on the Label tab.
      4. Ensure the labels are shown.
      5. Stagger the odd labels.
      6. Right-click on graph.
    11. Choose Insert Trend-line.
      1. Go to Trend-line properties.
      2. Type: Choose Logarithmic.
      3. Check Show Equation.
      4. Click the Line tab.
      5. Choose the color magenta4.
      6. Width-.04.
  3. Use a line graph to chart the $ Change of your stocks throughout the project.  Use the $ Change columns in your project for your data.
  4. Double-check the rest of your project.
    • Ensure all necessary formulas are present.
    • Ensure all stock prices are correct based on the given date/time.
    • Ensure all other requirements are met, as this is your Final Submission.
  5. Due Feb 1 in specified folder.