Stock Challenge

1
Stock Challenge Directions Part 1 Labeling
Categories and Format the Basic Setup

• Dont forget to continue to autosize you columns
  as you see text overlap or not fit in a cell. If
  you see symbols, it means the text is too
  wide to be seen and autosize will fix that as
  well.

• Change the tab name at the bottom of the sheet to
  Stocks. Right click on the tab name and give it a
  Tab Color

2

• In cell A1, type the text Todays Date
• Use ControlB to bold the text in A1 and raise
  the font size to 12
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around this
  cell.

3

• Select cells D1 and E1. On the Home tab,
  Alignment section, select Merge Center.
• In the merged cells, type the text Gift.
• Use ControlB to bold the text, raise the font to
  size 12.
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around these
  merged cells.

4

• Select cells F1 and G1. On the Home tab,
  Alignment section, select Merge Center.
• In the merged cells, put the number 10,000.
• Bold and raise the font size to 12.
• Use the Control1, the Number tab, and select
  Currency to format the amount. Make sure the
  sign is selected in the Symbol box.
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around these
  merged cells.

5

• Select cells B3, C3, D3, E3. On the Home tab,
  Alignment section, select Merge Center.
• In the merged cells, type the text Purchase
  Information
• Use ControlB to bold the text in the merged
  cells, raise the font size to 12.
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around these
  merged cells.

6

• Select cells F3, G3, H3, and I3. On the Home tab,
  Alignment section, select Merge Center.
• In the merged cells, type the text Current
  Information
• Use ControlB to bold the text in the merged
  cells, raise the font size to 12.
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around these
  cells.

7

• In cell A4, type the text Company
• In cell B4, type the text Date
• In cell C4, type the text Shares
• In cell D4, type the text Price
• In cell E4, type the text Cost

• In cell F4, type the text Price
• In cell G4, type the text Value
• In cell H4, type the text Gain/Loss
• In cell I4, type the text Days Held

Select the text in A4 trough I4 and bold it using
ControlB
8

• Select cells D5 through H11.
• Use Control1, Number tab, and select Currency.
• The Decimal places should read 2, Symbol should
  be a , and choose the red selection without
  parentheses for Negative numbers.

9

• Select cells A4 through I9.
• Use the border icon on the toolbar or Control1
  and the Border tab, All Borders to put borders
  around each of the cells in the range.

10

• In cell A11, type the text Total
• Bold and raise the font size of this text to 12.
• Select cells A11, E11, G11, and H11 (remember,
  using the control key lets you select
  non-contiguous cells).
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around these
  cells.

11
You should now have a document that looks like
the picture below If not, carefully go back and
find out what direction you missed.
12
Stock Challenge Directions Part 2 Formulas and
Retrieving External Data
Inserting Formulas (dont forget to use the Enter
or Return key after typing a formula)

• In cell B1, type the formula TODAY()
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around this
  cell.

13

• In the cell H1, type the formula F1-E11
• Use the Borders dropdown on your toolbar and
  select the Thick Box Border to put around this
  cell.
• Format the cell by using Control1, Number tab,
  and select Currency.
• The Decimal places should read 2, Symbol should
  be a , and choose the red selection without
  parentheses for Negative numbers.

14

• In cell E5, type the formula C5D5
• Use the smart copy and paste feature to put
  this formula in E6 through E9

15

• In cell G5, put the formula F5C5
• Use smart copy and paste to put this formula in
  G6 through G9

16

• In cell H5, put the formula G5-E5
• Use smart copy and paste to put this formula in
  H6 through H9

17

• In cell E11, put the formula SUM(E5E9)
• (This can also be done by selecting E11, the
  Sigma key on the toolbar and then selecting E5
  through E9)

18

• In cell G11, put the formula SUM(G5G9)

19

• In cell H11, put the formula G11-E11

20

• In cell I5, put the formula B1-B5
• After entering this formula autosize the column.
• To make the absolute value read correctly, click
  the comma symbol on the toolbar under the Home
  Tab in the Number section.

21

• Click the Decrease Decimal icon on the toolbar in
  the same section to get rid of decimals if they
  appear. Dont worry if you get an odd number
  this will fix itself after one day on its own.

22
Use smart copy and paste to put this formula in
I6 through I9 (dont worry if the cell gives you
a strange answer with that formula ... it will
eventually fix itself as we go further in the
directions and time passes) .
23

• Conditional Formatting to add stationary
  background color to rows.
• Select cells B3-I3. Then, hold down the control
  key and also select A4 through I9.
• Select the Conditional Formatting dropdown
• Select New Rule
• Select Use a formula to determine which cells to
  format.
• In Format values where this formula is true put
  the formula
• MOD(ROW(),2)
• Choose the background fill color by selecting
  Format then from the Fill tab, select a light
  shade pastel color.

24

• Follow the same process again select the same
  cells, and conditional format with a second
  formula
• AND(MOD(ROW(),2)0)
• Shade this formula with an opposing light shade
  pastel color You will end up with something like
  the picture below.

25

• Select cells A11, E11, G11, and H11 (remember,
  using the control key lets you select
  non-contiguous cells).
• Use the paintbucket icon on the toolbar to choose
  a contrasting light pastel fill color that is
  different from the other 2 previously chosen.

26
Stock Challenge Directions Part 3 Shop For
StocksDo you feel lucky !!!

• Choose 5 companies from the stock market to
  invest your 10,000 gift. They must be publicly
  traded on NYSE.
• Use the link to the NYSE to search for company
  symbols.
• Use the links under Subsector to choose the
  types of businesses you would like to invest in.

27

• Use the Symbol column to get their call letters
  for your Excel chart and the Last Trade column
  for the price per share you will pay.

28

• In cells A5, A6, A7, A8, and A9 record the call
  letters for your 5 companies.
• In B5, B6, B7, B8, and B9 record todays date when
  you picked each company using the MM/DD/YYYY
  format.

29

• Record the Purchase price for each company
  shares in D5, D6, D7, D8, and D9 by going to the
  links where you found your company.

30

• In cells C5, C6, C7, C8, and C9 select the number
  of shares you would like to buy. Keep an eye on
  cell H1 as you do this. You may not let the
  number in that cell go below 0. Get as close to 0
  as possible by adjusting your shares. If it turns
  red, you must decrease your shares of stock until
  it is a positive number.
• Ignore what happens in H5 through H11 that will
  fix itself in the next directive.

31

• Retrieve External Data
• Select cell A14
• Under the DATA tab, in the Get External Data
  section select Existing Connections, MSN
  MoneyCentral Investor Stock Quotes.

32

• When the Import Data box appears, it should say
  A14
• If it does, click the Properties tab in the lower
  left and change the Refresh control section by
  checking the box that says Refresh data when
  opening the file.

33

• When the Enter Parameter Value box appears, use
  your mouse to select cells A5 through A9.
• The formula Stocks!A5A9 should
  appear in the box (another way would be to type
  each companies symbols and separate each with a
  comma and a space.

• Check the box below it that says Use this value/
  reference for future refreshes, then click OK.

34
Your spreadsheet should now look something like
this.
35

• Select cell F5. Type the sign and then click
  the price for that company in the column that
  says Last in the chart that just appeared (now
  F5 should read D17 in the fx formula
  bar.

36

• If it does, smart copy and paste this formula
  from F6 through F9. Fix the fill colors that
  change as a result of this.

37
Dont forget to autosize your columns 1 last
time. You should now have a file that looks like
this
Save your file in your H drive Email to your
instructor. Be sure to follow business email
protocol.
Every once in a while, you might be curious and
want to open the file and see how your stocks are
doing. It is fully automated now and will give
you new totals every time you open it. On exam
day, lets see who made the most.