Assignment 1 Bitcoin market data

Data Analytics: Principles and Tools

Total: 70 Points (5% of Final Grade)
Learning Outcomes
By completing this assignment, you will gain and demonstrate skills relating to:
• Retrieving web data.
• Formatting spreadsheets.
• Computing using Excel.
• Performing an RSI Analysis.
Instructions
In this assignment, you will download historical Bitcoin market data from
CoinMarketCap.com and import it into Excel. You will then format the data, perform a series
of calculations on the data and a Relative Strength Index (RSI) Analysis.
You are required to follow each step in this assignment and submit both an Excel .xlsx and
PDF copy of your assignment. You must assume that the data in your sheet can change (i.e.
you may not hardcode your answers). Each step must be followed precisely including the file
naming convention given in Part 8. You will be assessed on the following:
• Retrieving the correct data.
• Your formatting of the spreadsheet.
• Completion of each task correctly.
• Assignment submission via OWL.
Background
Bitcoin is a cryptocurrency created in 2009 by an anonymous person under the alias Satoshi
Nakamoto. It has become popular with speculators in recent years due to its dramatic
increase in value, rising from $0.30 USD in 2011 to approximately $20,000 USD at the end of
2017. Today, prices are closer to $4,000 USD after a significant decline due to several factors
including China banning the trading of Bitcoins and multiple hacks and thefts from
cryptocurrency exchanges.
CS2034 - Data Analytics: Principles and Tools Assignment #1
1 of 7
Cryptocurrencies like Bitcoin are digital currencies combined with a decentralized payment
system that are mostly unregulated and pseudonymous. These “coins” exist only digitally in
a distributed on-line public ledger called the blockchain. Bitcoins and other cryptocurrencies
are traded for “real” money on a number of exchanges with no governmental oversight or
control.
Tasks
1. Get The Historical Bitcoin Data For 2018
CoinMarketCap.com provides historical Bitcoin price data dating back to April 2013. Visit the
following link and select the date range January 1st, 2018 to December 31st, 2018
(inclusive):
https://coinmarketcap.com/currencies/bitcoin/historical-data
Copy the table into a new Excel sheet including the headers (Date, Open, High, etc.) by copy
and pasting it from your web browser. In this case there should be no need to clean the data
before pasting it into Excel, however, this is not always the case (as we will see in the labs).
2. Format the Data Nicely (10 Marks)
Highlight all of the data (including headers) in your table, right click on the “Sort” button on
the data tab. It should look like this:
Sort the data by date from oldest to newest. Make sure you have “My data has headers”
checked.
Adjust the column widths so that all data is visible, i.e. there are no cells showing “#####”.
Adjust the precision of all the cells in the “Open”, “High”, “Low” and “Close” columns to show
two decimal places and format them as US currency. Format the dates to be in yearmonthday style (e.g. 2018-01-01 for January 1st, 2018). Clean up the column headers (remove *s)
and make them centered, in bold, with a grey background. Put black grid lines around the
entries in the table. Insert 5 blank lines above the table. Add a title in the 1st row “Bitcoin
Market Data by [Your Name]” in a large font and bold. Of course “[Your Name]” should be
replaced with your name.
CS2034 - Data Analytics: Principles and Tools Assignment #1
2 of 7
3. Compute the Range (10 Marks)
Put the text “Range” in cell A2. In cell B2 put a formula that calculates the minimum value
from the “Low” column. In cell C2 put a formula that calculates the maximum value from the
“High” column.
Put the text “Range Date” in cell A3. In cell B3 use the INDEX and MATCH functions to find
the date with the smallest low. In cell C3 use the same functions to find the date with the
largest high. Format the resulting values as dates.
Hint: If you fail to format the cells as dates INDEX and MATCH will return a number rather than
a date.
4. Measure the Volatility (10 Marks)
Add a new column to the right of the table (i.e. in column H) that computes the Daily
Logarithmic Return for each date by using equation 1:
Equation 1:
where Ci is the closing price for the current day, Ci−1 is the closing price for the previous day
and ln is the natural logarithm. There should be no value for the first row as there is no
preceding day. Give this column the header “Daily Return” and format the column and header
as in Step 3.
In cell A4 put the text “STDev” and in cell B4 calculate the standard deviation of the daily
returns. In cell A5 put the text “Volatility” and compute
√ days× STDev
(sqrt of days X STDev)
in cell B5, where days is the number of days in the year that Bitcoin is traded (make this
dynamic based on the number of dates we have in our data set and not a hardcoded value)
and STDev is a reference to the cell in which we calculated the standard deviation. This gives
us the annualized historical volatility. Format volatility value and the values in the daily
change column as percentages.
Volatility is a technical indicator of the amount of uncertainty or risk about the size of
changes in a currency, stock or other security’s value. A higher volatility means the price is
more likely to change dramatically over a short time period. A lower volatility means the
CS2034 - Data Analytics: Principles and Tools Assignment #1
3 of 7
price is more stable and less likely to fluctuate dramatically in a short time period. For
comparison, gold has an average annualized historical volatility of approximately 15%.
5. Relative Strength Index Analysis
Relative Strength Index (RSI) is a technical indicator that compares the magnitude of recent
gains and losses over a set time to give an indication of the momentum of stock, currency or
other security. It is used by traders to identify securities that are potentially overbought or
oversold. In theory, an overbought security is overvalued and prime for a downwards
reversal (the price will drop), likewise an oversold security is undervalued and prime for an
upwards reversal (the price will rise).
The relative strength index is calculated using the following formula:
Equation 2:
RSI
where RS is the relative strength and is calculated as follows:
Equation 3:
Average gain of up periods
RS =
Average loss of down periods
5.1. Find the Gains and Losses (10 Marks)
To make this calculation in Excel we will first need to add two columns to the right of the
table (columns I and J) to determine the Gain or Loss for the day. Give the first column
(column I) the header “Gain” and the second (column J) the header “Loss”. In the gains
column enter an equation that will find the gain (today’s closing price minus yesterday’s
closing price) if and only if the daily change is positive, otherwise the result will be zero.
Similarly, in the loss column enter an equation that will find the loss (also based on today’s
and yesterday’s closing price) if and only if the daily change is negative, otherwise the result
will be zero. Make sure both gain and loss are positive numbers.
Hint: You will need to use the Excel IF function.
5.2. Compute the 14-Day Average Gains and Losses (10 Marks)
Relative strength is traditionally calculated over a 14 day period, so in the next two columns
(columns K and L) we will find the average gain and loss over a 14 day period. Add the
CS2034 - Data Analytics: Principles and Tools Assignment #1
4 of 7
headers “Avg Gain” and “Avg Loss” to these columns and format them appropriately. As we
are calculating the average for the first 14 days, the first 14 rows in the table will be blank. In
cell K21 find the average of the first 14 gains. In cell L21 find the average of the first 14 losses.
To calculate the remaining averages following this formula:
Equation 4:
(Last Average × 13) + Today’s Gain or Loss
14 Day Average
5.3. Compute the Relative Strength (RS) and Relative Strength Index (RSI) (10 Marks)
Now that we have the average gains and losses we can calculate the relative strength (RS) by
dividing the average gain by the average loss for that day (as shown in Equation 3). Add a
new column to the right (column M) with the header “RS”. In this column find the RS for each
day by dividing the value you calculated for Avg Gain by the value you calculated for Avg Loss
on the same row.
We can now finally compute the 14-day RSI for each date by following Equation 2. Add a new
column to the right (column N) and give it the header “RSI”. In this column calculate the RSI
using Equation 2 and the RS value you calculated.
6. Analysis (10 Marks)
Add a new worksheet named ”Analysis”. On this sheet add a line graph of the Bitcoin Closing
Price each day and a line graph of the RSI each day (starting on January 15th). Make sure the
graphs are properly titled and the axis are labelled. Ignore the red and green lines in the
second graph. The result should look roughly like this:
CS2034 - Data Analytics: Principles and Tools Assignment #1
5 of 7
8. Submission
Failing to follow the submission instructions can lead to a mark penalty. Marks will be
deducted for incorrectly named files, files not submitted to OWL correctly, missing PDF, etc.
Save your Excel file a .xlsx file and name it “userid assign1.xlsx” where userid is your user id.
For example, if your uwo e-mail was “cbrogly@uwo.ca”, the file should be named
“cbrogly_assign1.xlsx”.
Create a PDF of your Excel file that shows all of your columns and name it “userid assign1.pdf”
where userid is your user id. To ensure that all of the columns are shown go under the File tab,
select Print on the left, and then select the box that says “No Scaling”. You should then be
presented with various scaling options. Select “Fit All Columns on One Page” before saving the
PDF.
In terms of formatting, your PDF document should look similar to the following screen shot.
Make sure all headings and columns are formatted as specified in Step 3 and that all values
have a sensible format (currency, percentage, etc.).
If you are not using the Windows version of Excel 2016, check that your Excel document
functions correctly on a GenLabs computer running Excel 2016. It is up to you to ensure
compatibility.
Submit both userid assign1.xlsx and userid assign1.pdf via OWL.
Example Output
Some values blurred to not give away answers. Only first worksheet and first 24 rows shown.