-
Notifications
You must be signed in to change notification settings - Fork 0
/
05-time-series.Rmd
115 lines (69 loc) · 5.7 KB
/
05-time-series.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
# Time Series
A *time series* is a series of data points indexed (or listed or graphed) at successive equally spaced points in time.
In this lab, you will build a time series using Excel. In addition, as practice, you will use Excel to calculate the measures of tendency and variation.
## Preparation
Below are some sources you can use for this lab. The websites below are financial sites for stock or index (a financial assest) prices and business information. Choose one asset and collect *30 related values or more data values* to complete this lab.
* [**www.nasdaq.com**](www.nasdaq.com)
Select a stock. On the left side menu in the webpage, there is the option for its historical quote, then download data.
* [**www.finance.yahoo.com**](www.finance.yahoo.com)
Go to the tab *Markets*, select the type of asset (e.g., *Trending Tickets*), then go to the tab *Historical Data* to download the data.
* [**https://www.investing.com**](https://www.investing.com)
Select an asset available on the website. Go to *Historical Data* in the tab *General* to obtain the data set.
## Download the Data
1. After choosing one of the financial sites to obtain your data, select an asset to download its prices.
2. Once you have selected an asset , choose a range of 30 or more consecutive days of prices.
3. Download the data into an Excel file.
## Preparing the Data
You will label each day in the data set with sequential numbers from 1 to 30 (or more than 30, depending on your data).
The asset's *Close*, *Open*, *Low*, and *High* values are contained in the file.
### Renaming the File
1. Open the file you downloaded from the website.
2. Go to `File > Save As`. Include *Lab 5* in the file’s name and save it with the extension *.xlsx*.
### Inserting a Column on MAC
Next, you will insert a new column between the columns with *Dates* and *Prices*. The following steps will prompt Excel to insert a new column with all the columns with prices to the right of it.
1. Hold down `CONTROL` and simultaneously click the first column with Prices (e.g., Open).
2. Then on the pop-up menu, click `Insert`.
3. Type the label *Day* at the top of the new column.
```{r insert-column-mac, echo=FALSE, fig.align = 'center', out.width='60%', fig.show='hold', fig.cap='Insert column menu on MAC.', fig.alt = 'A screenshot of the insert column menu on MAC.'}
knitr::include_graphics('images/insert-column-mac.png')
```
### Inserting a Column on PC
1. `RIGHT-click` the top of the first column with Prices.
2. Select `Insert`.
3. Type the label *Day* at the top of the new column.
### Creating a List of Sequential Numbers {#seq-numbers}
Next, you will create a list in the new column with numbers 1 to 30 (or more, depending on your data).
1. Enter 1 (starting value of the series) in the cell below the label Day in the new column.
**Note:** If the number 1 changes to a date format, select the column and change it to the `General` format in the `Number` ribbon.
```{r numbers-ribbon, echo=FALSE, fig.align = 'center', out.width='35%', fig.show='hold', fig.cap='Numbers ribbon in the Home tab.', fig.alt = 'A screenshot of the Numbers ribbon in the Home tab.'}
knitr::include_graphics('images/numbers-ribbon.png')
```
2. Type the number *2* in the next cell below to establish a pattern.
3. Select the cells that contain the two starting values.
4. Position the mouse pointer in the lower right corner of the selected cells until it becomes a `+` sign and click-drag downward across the range you want to fill.
## Time Series
The following steps will guide you to build a time series for displaying your data. Use the asset’s *Close* prices to build the time series.
1. Select all cells in both Days and Close columns.
**Note:** If the columns with *Days* and *Close* prices are not adjacent (separate), first select the cells in one column, and then while holding the `Control` key on MAC (`Control` or `CTRL` on PC), select the cells in the other column.
2. Click `Insert > Scatter > Scatter with Straight Lines and Markers`.
```{r scatter-options, echo=FALSE, fig.align = 'center', out.width='95%', fig.show='hold', fig.cap='Scatter options in the Insert tab.', fig.alt = 'A screenshot of the chart options.'}
knitr::include_graphics('images/scatter-options.png')
```
The resulting graph needed to be adjusted.
3. Select the chart created.
4. Replace the *Chart Title* with an appropriate title for your time series. This depends on your data set.
5. With the graph selected, go to the `Chart Desig`n tab.
6. To add axis titles, click `Add Chart Element > Axis Titles > Primary Horizontal` and then `Add Chart Element > Axis Titles > Primary Vertical`.
7. Replace the Y-axis Title with an appropriate name for the vertical axis.
8. Replace the X-axis Title with the word *Day*.
```{r adjust-scatter, echo=FALSE, fig.align = 'center', out.width='95%', fig.show='hold', fig.cap='Chart adjustment options Chart Design.', fig.alt = 'A screenshot of the adjustment options for the chart.'}
knitr::include_graphics('images/adjust-scatter.png')
```
9. Make any adjustments necessary to the chart’s vertical and horizontal axes by `RIGHT-clicking` on them and selecting `Format Axis`.
```{r adjust-axis, echo=FALSE, fig.align = 'center', out.width='80%', fig.show='hold', fig.cap='Chart adjustment of the horizontal axis.', fig.alt = 'A screenshot of the Format Axis options.'}
knitr::include_graphics('images/adjust-axis.png')
```
## Practice 1
Use Excel to build a time series for the *High* prices in your data set.
## Practice 2
Use Excel to calculate your data set's mean, mode, median and sample standard deviation of the *Close* prices. Use *Lab* ***\@ref(central-tendency-variation)*** *Measures of Central Tendency and Variation*'s instructions on how to calculate these measures.