<jupyter_start><jupyter_text>Crypto ArbitrageIn this Challenge, you'll take on the role of an analyst at a high-tech investment firm. The vice president (VP) of your department is considering arbitrage opportunities in Bitcoin and other cryptocurrencies. As Bitcoin trades on markets across the globe, can you capitalize on simultaneous price dislocations in those markets by using the powers of Pandas?For this assignment, you’ll sort through historical trade data for Bitcoin on two exchanges: Bitstamp and Coinbase. Your task is to apply the three phases of financial analysis to determine if any arbitrage opportunities exist for Bitcoin.This aspect of the Challenge will consist of 3 phases.1. Collect the data.2. Prepare the data.3. Analyze the data.    Import the required libraries and dependencies.<jupyter_code>import pandas as pd
from pathlib import Path
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Collect the DataTo collect the data that you’ll need, complete the following steps:Instructions. 1. Using the Pandas `read_csv` function and the `Path` module, import the data from `bitstamp.csv` file, and create a DataFrame called `bitstamp`. Set the DatetimeIndex as the Timestamp column, and be sure to parse and format the dates.2. Use the `head` (and/or the `tail`) function to confirm that Pandas properly imported the data.3. Repeat Steps 1 and 2 for `coinbase.csv` file.  Step 1: Using the Pandas `read_csv` function and the `Path` module, import the data from `bitstamp.csv` file, and create a DataFrame called `bitstamp`. Set the DatetimeIndex as the Timestamp column, and be sure to parse and format the dates.<jupyter_code># Read in the CSV file called "bitstamp.csv" using the Path module. 
# The CSV file is located in the Resources folder.
# Set the index to the column "Date"
# Set the parse_dates and infer_datetime_format parameters
bitstamp = pd.read_csv(Path('../Starter_Code/Resources/bitstamp.csv'),index_col='Timestamp',parse_dates=True, infer_datetime_format=True)<jupyter_output><empty_output><jupyter_text>Step 2: Use the `head` (and/or the `tail`) function to confirm that Pandas properly imported the data.<jupyter_code># Use the head (and/or tail) function to confirm that the data was imported properly.
bitstamp.head()
bitstamp.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 129540 entries, 2018-01-01 00:00:00 to 2018-03-31 23:59:00
Data columns (total 7 columns):
 #   Column          Non-Null Count   Dtype  
---  ------          --------------   -----  
 0   Open            129067 non-null  float64
 1   High            129067 non-null  float64
 2   Low             129067 non-null  float64
 3   Close           129540 non-null  object 
 4   BTC Volume      129067 non-null  float64
 5   USD Volume      129067 non-null  float64
 6   Weighted Price  129067 non-null  float64
dtypes: float64(6), object(1)
memory usage: 7.9+ MB<jupyter_text>Step 3: Repeat Steps 1 and 2 for `coinbase.csv` file.<jupyter_code># Read in the CSV file called "coinbase.csv" using the Path module. 
# The CSV file is located in the Resources folder.
# Set the index to the column "Timestamp"
# Set the parse_dates and infer_datetime_format parameters
coinbase = pd.read_csv(Path('../Starter_Code/Resources/coinbase.csv'),index_col='Timestamp',parse_dates=True, infer_datetime_format=True)
# Use the head (and/or tail) function to confirm that the data was imported properly.
coinbase.tail()<jupyter_output><empty_output><jupyter_text>Prepare the DataTo prepare and clean your data for analysis, complete the following steps:1. For the bitstamp DataFrame, replace or drop all `NaN`, or missing, values in the DataFrame.2. Use the `str.replace` function to remove the dollar signs ($) from the values in the Close column.3. Convert the data type of the Close column to a `float`.4. Review the data for duplicated values, and drop them if necessary.5. Repeat Steps 1–4 for the coinbase DataFrame.  Step 1: For the bitstamp DataFrame, replace or drop all `NaN`, or missing, values in the DataFrame.<jupyter_code># For the bitstamp DataFrame, replace or drop all NaNs or missing values in the DataFrame
bitstamp = bitstamp.dropna().copy()
bitstamp.isnull().sum()<jupyter_output><empty_output><jupyter_text>Step 2: Use the `str.replace` function to remove the dollar signs ($) from the values in the Close column.<jupyter_code># Use the str.replace function to remove the dollar sign, $
bitstamp['Close'] = bitstamp['Close'].str.replace('$','',regex=True)
bitstamp.head()<jupyter_output><empty_output><jupyter_text>Step 3: Convert the data type of the Close column to a `float`.<jupyter_code># Convert the Close data type to a float
bitstamp['Close'] = bitstamp['Close'].astype('float')
bitstamp.dtypes<jupyter_output><empty_output><jupyter_text>Step 4: Review the data for duplicated values, and drop them if necessary.<jupyter_code># Review the data for duplicate values, and drop them if necessary
bitstamp = bitstamp.drop_duplicates()
bitstamp.duplicated()<jupyter_output><empty_output><jupyter_text>Step 5: Repeat Steps 1–4 for the coinbase DataFrame.<jupyter_code># Repeat Steps 1–4 for the coinbase DataFrame
coinbase.isnull().sum()
coinbase = coinbase.dropna().copy()
coinbase['Close'] = coinbase['Close'].str.replace('$','', regex=True)
coinbase['Close'] = coinbase['Close'].astype('float')
coinbase = coinbase.drop_duplicates()
coinbase.dtypes<jupyter_output><empty_output><jupyter_text>Analyze the DataYour analysis consists of the following tasks: 1. Choose the columns of data on which to focus your analysis.2. Get the summary statistics and plot the data.3. Focus your analysis on specific dates.4. Calculate the arbitrage profits.  Step 1: Choose columns of data on which to focus your analysis.Select the data you want to analyze. Use `loc` or `iloc` to select the following columns of data for both the bitstamp and coinbase DataFrames:* Timestamp (index)* Close<jupyter_code># Use loc or iloc to select `Timestamp (the index)` and `Close` from bitstamp DataFrame
bitstamp_sliced = bitstamp.loc[:,['Close']]

# Review the first five rows of the DataFrame
bitstamp_sliced.head()
# Use loc or iloc to select `Timestamp (the index)` and `Close` from coinbase DataFrame
coinbase_sliced = coinbase.loc[:,['Close']]

# Review the first five rows of the DataFrame
coinbase_sliced.head()<jupyter_output><empty_output><jupyter_text>Step 2: Get summary statistics and plot the data.Sort through the time series data associated with the bitstamp and coinbase DataFrames to identify potential arbitrage opportunities. To do so, complete the following steps:1. Generate the summary statistics for each DataFrame by using the `describe` function.2. For each DataFrame, create a line plot for the full period of time in the dataset. Be sure to tailor the figure size, title, and color to each visualization.3. In one plot, overlay the visualizations that you created in Step 2 for bitstamp and coinbase. Be sure to adjust the legend and title for this new visualization.4. Using the `loc` and `plot` functions, plot the price action of the assets on each exchange for different dates and times. Your goal is to evaluate how the spread between the two exchanges changed across the time period that the datasets define. Did the degree of spread change as time progressed?<jupyter_code># Generate the summary statistics for the bitstamp DataFrame
bitstamp.describe()
# Generate the summary statistics for the coinbase DataFrame
coinbase.describe()
# Create a line plot for the bitstamp DataFrame for the full length of time in the dataset 
# Be sure that the figure size, title, and color are tailored to each visualization
bitstamp_sliced.plot(title='Bitstamp Prices', xlabel='Date',ylabel='Daily Close',color='green',figsize=(10, 5)).legend(['Bitstamp Close'])
# Create a line plot for the coinbase DataFrame for the full length of time in the dataset 
# Be sure that the figure size, title, and color are tailored to each visualization
coinbase_sliced.plot(title='Coinbase Prices', xlabel='Date',ylabel='Daily Close',color='red',figsize=(10, 5)).legend(['Coinbase Close'])
# Overlay the visualizations for the bitstamp and coinbase DataFrames in one plot
# The plot should visualize the prices over the full lenth of the dataset
# Be sure to include the parameters: legend, figure size, title, and color and label
bitstamp_sliced['Close'].plot(legend=True,figsize=(10, 5), color='green')
coinbase_sliced['Close'].plot(title='Coinbase v Bitstamp Closing', xlabel='Date',ylabel='Daily Close', legend=True,figsize=(10, 5), color='red').legend(['Bitstamp','Coinbase'])
# Using the loc and plot functions, create an overlay plot that visualizes 
# the price action of both DataFrames for a one month period early in the dataset
# Be sure to include the parameters: legend, figure size, title, and color and label
bitstamp_sliced['Close'].loc['2018-01-01':'2018-02-01'].plot(legend=True,figsize=(10, 5), color='green')
coinbase_sliced['Close'].loc['2018-01-01':'2018-02-01'].plot(title='Coinbase v Bitstamp Closing', xlabel='Date',ylabel='Daily Close', legend=True,figsize=(10, 5), color='red').legend(['Bitstamp','Coinbase'])
# Using the loc and plot functions, create an overlay plot that visualizes 
# the price action of both DataFrames for a one month period later in the dataset
# Be sure to include the parameters: legend, figure size, title, and color and label 
bitstamp_sliced['Close'].loc['2018-03-01':'2018-04-01'].plot(legend=True,figsize=(10, 5), color='green')
coinbase_sliced['Close'].loc['2018-03-01':'2018-04-01'].plot(title='Coinbase v Bitstamp Closing', xlabel='Date',ylabel='Daily Close', legend=True,figsize=(10, 5), color='red').legend(['Bitstamp','Coinbase'])<jupyter_output><empty_output><jupyter_text>**Question** Based on the visualizations of the different time periods, has the degree of spread change as time progressed?**Answer** The degree of spread is inconsistent. For the most part, the closing prices are tightly correlated. The main period of divergence is from Jan 27 to Jan 30, but there are also other days on which a slight spread is visible.   Step 3: Focus Your Analysis on Specific DatesFocus your analysis on specific dates by completing the following steps:1. Select three dates to evaluate for arbitrage profitability. Choose one date that’s early in the dataset, one from the middle of the dataset, and one from the later part of the time period.2. For each of the three dates, generate the summary statistics and then create a box plot. This big-picture view is meant to help you gain a better understanding of the data before you perform your arbitrage calculations. As you compare the data, what conclusions can you draw?<jupyter_code># Create an overlay plot that visualizes the two dataframes over a period of one day early in the dataset. 
# Be sure that the plots include the parameters `legend`, `figsize`, `title`, `color` and `label` 
bitstamp_sliced['Close'].loc['2018-01-13':'2018-01-13'].plot(legend=True,figsize=(10, 5), color='green')
coinbase_sliced['Close'].loc['2018-01-13':'2018-01-13'].plot(title='Coinbase v Bitstamp Closing', xlabel='Date',ylabel='Daily Close', legend=True,figsize=(10, 5), color='red').legend(['Bitstamp','Coinbase'])
# Using the early date that you have selected, calculate the arbitrage spread 
# by subtracting the bitstamp lower closing prices from the coinbase higher closing prices
arbitrage_spread_early = coinbase_sliced['Close'].loc['2018-01-13':'2018-01-13'] - bitstamp_sliced['Close'].loc['2018-01-13':'2018-01-13']

# Generate summary statistics for the early DataFrame
arbitrage_spread_early.describe()
# Visualize the arbitrage spread from early in the dataset in a box plot
arbitrage_spread_early.plot.box()
# Create an overlay plot that visualizes the two dataframes over a period of one day from the middle of the dataset. 
# Be sure that the plots include the parameters `legend`, `figsize`, `title`, `color` and `label` 
bitstamp_sliced['Close'].loc['2018-01-28':'2018-01-28'].plot(legend=True,figsize=(10, 5), color='green')
coinbase_sliced['Close'].loc['2018-01-28':'2018-01-28'].plot(title='Coinbase v Bitstamp Closing', xlabel='Date',ylabel='Daily Close', legend=True,figsize=(10, 5), color='red').legend(['Bitstamp','Coinbase'])
# Using the date in the middle that you have selected, calculate the arbitrage spread 
# by subtracting the bitstamp lower closing prices from the coinbase higher closing prices
arbitrage_spread_middle = coinbase_sliced['Close'].loc['2018-01-28':'2018-01-28'] - bitstamp_sliced['Close'].loc['2018-01-28':'2018-01-28']

# Generate summary statistics 
arbitrage_spread_middle.describe()
# Visualize the arbitrage spread from the middle of the dataset in a box plot
arbitrage_spread_middle.plot.box()
# Create an overlay plot that visualizes the two dataframes over a period of one day from late in the dataset. 
# Be sure that the plots include the parameters `legend`, `figsize`, `title`, `color` and `label` 
bitstamp_sliced['Close'].loc['2018-03-29':'2018-03-29'].plot(legend=True,figsize=(10, 5), color='green')
coinbase_sliced['Close'].loc['2018-03-29':'2018-03-29'].plot(title='Coinbase v Bitstamp Closing', xlabel='Date',ylabel='Daily Close', legend=True,figsize=(10, 5), color='red').legend(['Bitstamp','Coinbase'])
# Using the date from the late that you have selected, calculate the arbitrage spread 
# by subtracting the bitstamp lower closing prices from the coinbase higher closing prices
arbitrage_spread_late = coinbase_sliced['Close'].loc['2018-03-29':'2018-03-29'] - bitstamp_sliced['Close'].loc['2018-03-29':'2018-03-29']

# Generate summary statistics for the late DataFrame
arbitrage_spread_late.describe()
# Visualize the arbitrage spread from late in the dataset in a box plot
arbitrage_spread_late.plot.box()<jupyter_output><empty_output><jupyter_text>Step 4: Calculate the Arbitrage ProfitsCalculate the potential profits for each date that you selected in the previous section. Your goal is to determine whether arbitrage opportunities still exist in the Bitcoin market. Complete the following steps:1. For each of the three dates, measure the arbitrage spread between the two exchanges by subtracting the lower-priced exchange from the higher-priced one. Then use a conditional statement to generate the summary statistics for each arbitrage_spread DataFrame, where the spread is greater than zero.2. For each of the three dates, calculate the spread returns. To do so, divide the instances that have a positive arbitrage spread (that is, a spread greater than zero) by the price of Bitcoin from the exchange you’re buying on (that is, the lower-priced exchange). Review the resulting DataFrame.3. For each of the three dates, narrow down your trading opportunities even further. To do so, determine the number of times your trades with positive returns exceed the 1% minimum threshold that you need to cover your costs.4. Generate the summary statistics of your spread returns that are greater than 1%. How do the average returns compare among the three dates?5. For each of the three dates, calculate the potential profit, in dollars, per trade. To do so, multiply the spread returns that were greater than 1% by the cost of what was purchased. Make sure to drop any missing values from the resulting DataFrame.6. Generate the summary statistics, and plot the results for each of the three DataFrames.7. Calculate the potential arbitrage profits that you can make on each day. To do so, sum the elements in the profit_per_trade DataFrame.8. Using the `cumsum` function, plot the cumulative sum of each of the three DataFrames. Can you identify any patterns or trends in the profits across the three time periods?(NOTE: The starter code displays only one date. You'll want to do this analysis for two additional dates).  1. For each of the three dates, measure the arbitrage spread between the two exchanges by subtracting the lower-priced exchange from the higher-priced one. Then use a conditional statement to generate the summary statistics for each arbitrage_spread DataFrame, where the spread is greater than zero.*NOTE*: For illustration, only one of the three dates is shown in the starter code below.<jupyter_code># For the date early in the dataset, measure the arbitrage spread between the two exchanges
# by subtracting the lower-priced exchange from the higher-priced one
arbitrage_spread_early = bitstamp_sliced['Close'].loc['2018-01-13'] - coinbase_sliced['Close'].loc['2018-01-13'] 
arbitrage_spread_middle = bitstamp_sliced['Close'].loc['2018-01-28'] - coinbase_sliced['Close'].loc['2018-01-28']
arbitrage_spread_late = bitstamp_sliced['Close'].loc['2018-03-29'] - coinbase_sliced['Close'].loc['2018-03-29']

# Use a conditional statement to generate the summary statistics for each arbitrage_spread DataFrame
print("---------\n| Early |\n---------\n",arbitrage_spread_early[arbitrage_spread_early > 0])
print("----------\n| Middle |\n----------\n",arbitrage_spread_middle[arbitrage_spread_middle > 0])
print("--------\n| Late |\n--------\n",arbitrage_spread_late[arbitrage_spread_late > 0])<jupyter_output>---------
| Early |
---------
 Timestamp
2018-01-13 00:15:00     4.96
2018-01-13 00:17:00    15.41
2018-01-13 00:24:00     9.20
2018-01-13 00:48:00     8.19
2018-01-13 00:52:00    57.82
                       ...  
2018-01-13 23:48:00    39.89
2018-01-13 23:50:00    41.07
2018-01-13 23:54:00    12.90
2018-01-13 23:55:00    37.39
2018-01-13 23:58:00     1.39
Name: Close, Length: 1019, dtype: float64
----------
| Middle |
----------
 Timestamp
2018-01-28 00:00:00    275.38
2018-01-28 00:01:00    275.00
2018-01-28 00:02:00    268.77
2018-01-28 00:03:00    275.03
2018-01-28 00:04:00    294.26
                        ...  
2018-01-28 23:55:00    220.10
2018-01-28 23:56:00    195.11
2018-01-28 23:57:00    208.74
2018-01-28 23:58:00    195.12
2018-01-28 23:59:00    192.63
Name: Close, Length: 1436, dtype: float64
--------
| Late |
--------
 Timestamp
2018-03-29 00:00:00     1.46
2018-03-29 00:01:00     3.92
2018-03-29 00:02:00     2.22
2018-03-29 00:03:00     2.24
2018-03-29 00:04:00     9.57[...]<jupyter_text>2. For each of the three dates, calculate the spread returns. To do so, divide the instances that have a positive arbitrage spread (that is, a spread greater than zero) by the price of Bitcoin from the exchange you’re buying on (that is, the lower-priced exchange). Review the resulting DataFrame.<jupyter_code># For the date early in the dataset, calculate the spread returns by dividing the instances when the arbitrage spread is positive (> 0) 
# by the price of Bitcoin from the exchange you are buying on (the lower-priced exchange).
spread_return_early = arbitrage_spread_early[arbitrage_spread_early>0] / coinbase_sliced['Close'].loc['2018-01-13']
spread_return_middle = arbitrage_spread_middle[arbitrage_spread_middle>0] / coinbase_sliced['Close'].loc['2018-01-28']
spread_return_late = arbitrage_spread_late[arbitrage_spread_late>0] / coinbase_sliced['Close'].loc['2018-03-29']
# Review the spread return DataFrame
print('---------\n| Early |\n---------\n',spread_return_early)
print('----------\n| Middle |\n----------\n',spread_return_middle)
print('--------\n| Late |\n--------\n',spread_return_late)<jupyter_output>---------
| Early |
---------
 Timestamp
2018-01-13 00:00:00         NaN
2018-01-13 00:01:00         NaN
2018-01-13 00:02:00         NaN
2018-01-13 00:03:00         NaN
2018-01-13 00:04:00         NaN
                         ...   
2018-01-13 23:55:00    0.002704
2018-01-13 23:56:00         NaN
2018-01-13 23:57:00         NaN
2018-01-13 23:58:00    0.000100
2018-01-13 23:59:00         NaN
Name: Close, Length: 1440, dtype: float64
----------
| Middle |
----------
 Timestamp
2018-01-28 00:00:00    0.023999
2018-01-28 00:01:00    0.023965
2018-01-28 00:02:00    0.023392
2018-01-28 00:03:00    0.023936
2018-01-28 00:04:00    0.025610
                         ...   
2018-01-28 23:55:00    0.019518
2018-01-28 23:56:00    0.017302
2018-01-28 23:57:00    0.018510
2018-01-28 23:58:00    0.017303
2018-01-28 23:59:00    0.017094
Name: Close, Length: 1440, dtype: float64
--------
| Late |
--------
 Timestamp
2018-03-29 00:00:00    0.000186
2018-03-29 00:01:00    0.000498
2018-03-29 00:02:00    0.[...]<jupyter_text>3. For each of the three dates, narrow down your trading opportunities even further. To do so, determine the number of times your trades with positive returns exceed the 1% minimum threshold that you need to cover your costs.<jupyter_code># For the date early in the dataset, determine the number of times your trades with positive returns 
# exceed the 1% minimum threshold (.01) that you need to cover your costs
profitable_trades_early = spread_return_early[spread_return_early > .01]
profitable_trades_middle = spread_return_middle[spread_return_middle > .01]
profitable_trades_late = spread_return_late[spread_return_late > .01]

# Review the first five profitable trades
print("Profitable trades early: ", len(profitable_trades_early))
print("Profitable trades middle:", len(profitable_trades_middle))
print("Profitable trades late:  ", len(profitable_trades_late))
print('----------\n| Middle |\n----------\n',profitable_trades_middle.head())<jupyter_output>Profitable trades early:  0
Profitable trades middle: 1378
Profitable trades late:   0
----------
| Middle |
----------
 Timestamp
2018-01-28 00:00:00    0.023999
2018-01-28 00:01:00    0.023965
2018-01-28 00:02:00    0.023392
2018-01-28 00:03:00    0.023936
2018-01-28 00:04:00    0.025610
Name: Close, dtype: float64<jupyter_text>4. Generate the summary statistics of your spread returns that are greater than 1%. How do the average returns compare among the three dates?<jupyter_code># For the date early in the dataset, generate the summary statistics for the profitable trades
# or you trades where the spread returns are are greater than 1%
profitable_trades_middle.describe()<jupyter_output><empty_output><jupyter_text>5. For each of the three dates, calculate the potential profit, in dollars, per trade. To do so, multiply the spread returns that were greater than 1% by the cost of what was purchased. Make sure to drop any missing values from the resulting DataFrame.<jupyter_code># For the date early in the dataset, calculate the potential profit per trade in dollars 
# Multiply the profitable trades by the cost of the Bitcoin that was purchased
profit_early = profitable_trades_early * coinbase_sliced['Close'].loc['2018-01-13']
profit_middle = profitable_trades_middle * coinbase_sliced['Close'].loc['2018-01-28']
profit_late = profitable_trades_late * coinbase_sliced['Close'].loc['2018-03-29']


# Drop any missing values from the profit DataFrame
profit_middle.dropna()

# View the profit DataFrame
print('----------\n| Middle |\n----------\n')
profit_middle<jupyter_output>----------
| Middle |
----------<jupyter_text>6. Generate the summary statistics, and plot the results for each of the three DataFrames.<jupyter_code># Generate the summary statistics for the profit per trade DataFrame
profit_middle.describe()
# Plot the results for the profit per trade DataFrame
profit_middle.plot(title='Profit per trade 28-Jan-2018', xlabel='Time',ylabel='Profit', legend=True,figsize=(10, 5), color='red')<jupyter_output><empty_output><jupyter_text>7. Calculate the potential arbitrage profits that you can make on each day. To do so, sum the elements in the profit_per_trade DataFrame.<jupyter_code># Calculate the sum of the potential profits for the early profit per trade DataFrame
profit_middle.sum()<jupyter_output><empty_output><jupyter_text>8. Using the `cumsum` function, plot the cumulative sum of each of the three DataFrames. Can you identify any patterns or trends in the profits across the three time periods?<jupyter_code># Use the cumsum function to calculate the cumulative profits over time for the early profit per trade DataFrame
cumulative_profit_middle = profit_middle.cumsum()
# Plot the cumulative sum of profits for the early profit per trade DataFrame
cumulative_profit_middle.plot(title='Cumulative profit 28-Jan-2018', xlabel='Time',ylabel='$', legend=True,figsize=(10, 5), color='green')<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup