Scribd Logo
Upload

Welcome to Scribd!

  • 299 views

    06a Charts

    Uploaded by

    api-200202067
    06. Create a scatter graph of returns using excel, we can find how the daily changes in one company are related to changes in the s&p500.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd

    06a Charts

    Uploaded by

    api-200202067
    299 views71 pages
    06. Create a scatter graph of returns using excel, we can find how the daily changes in one company are related to changes in the s&p500.

    Original Title

    06a-charts

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    06. Create a scatter graph of returns using excel, we can find how the daily changes in one company are related to changes in the s&p500.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    299 views71 pages

    06a Charts

    Uploaded by

    api-200202067
    06. Create a scatter graph of returns using excel, we can find how the daily changes in one company are related to changes in the s&p500.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 71

    06.

    Excel Charts

    File -> Open -> 06b-datastart.xlsx

    Click on column header A to select the Date column

    While holding Ctrl on your keyboard, click on column header E and F

    Click on Insert -> Line -> 2-D Line

    Displays a line graph of the indices both starting at 1,000

    Right-Click with mouse on left axis and click on Format Axis

    Choose Axis Options -> Major Unit Fixed 20000

    Choose Number -> 0 Decimal places

    Tick box for Use 1000 Separator

    Click Close

    Right-Click with mouse on X axis and click on Format Axis

    Click on Number and change Format Code to yyyy

    Click Add and then Close

    Right-Click on Legend and click Format Legend

    Untick box which says Show the legend without overlapping the chart

    Select Fill -> Solid Fill -> White

    Select Border Color -> Solid Line -> Black

    Click Close

    Click on Legend and drag to empty space on top left

    Right-Click on Plot Area and select Format Plot Area

    Select Fill -> Solid Fill -> White

    Select Border Color -> Solid Line -> Black

    Click Close

    Right-click on Chart Area and select Format Chart Area

    Click Fill -> No Fill

    Click Border Color -> No Line

    Click Close

    Now have a neatly formatted chart that can be copied to Word or Powerpoint

    Custom Charts

    Click on Chart

    Click Design -> Save as Template

    Save as CustomLineChart

    Click Save

    Click on Chart and press DEL on keyboard to delete

    Click on column header A to select the Date column

    While holding Ctrl on your keyboard, click on column header E and F

    Click on Insert -> Line -> All Chart Types

    Click on Templates and your custom chart

    Click OK

    Produces a chart using your template

    Click on chart and press DEL on keyboard

    Challenge: Create a scatter graph of returns

    We often want to find how the daily changes in one company are related to changes in the S&P500

    Type AppleReturn in H1

    Type SPReturn in I1

    In H3 type =(B3-B2)/B2

    In I3 type =(C3-C2)/C2

    Select cells H3 and I3 and press Ctrl+c on your keyboard

    Paste formula down remainder of dataset

    Click on column headers H and I

    Click on Format -> Format Cells

    Click on Percentage -> 2 Decimal Places

    Click OK

    Click on column header A, then hold Ctrl on your keyboard and select columns H and I

    Click on Insert -> Scatter

    Click on Chart

    Click on Layout -> Horizontal (Value) Axis

    Click on Format Selection

    Click on Axis Labels: Low

    Click Close

    Choose Layout -> Series AppleReturn

    Click Format Selection

    Choose Marker Options -> Built-in -> Size 3

    Click Close

    Choose Layout -> Series SPReturn

    Click Format Selection

    Click Marker Options -> Built-in -> Size 3

    Click Close

    File -> Save As -> 06c-datacompleted.xlsx

    Challenge
    Format the rest of the graph to make it neater
    Date format on axis Legend Plot area Chart area

    Save as CustomScatterGraph

    You might also like