Assignment 1 bitcoin market analysis solution

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










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 yearmonth-day 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.







1 of 7
哪个是daily change column??

CS2034 - Data Analytics: Principles and Tools Assignment #1










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




2 of 7
CS2034 - Data Analytics: Principles and Tools Assignment #1










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




3 of 7
CS2034 - Data Analytics: Principles and Tools Assignment #1










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.







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:






































































4 of 7
CS2034 - Data Analytics: Principles and Tools Assignment #1










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.












































































5 of 7