Hello World for [Crypto] Quant Traders

hello_crypto_world.py

#!/usr/local/bin/python
# based on the script by MR Jain at https://gist.github.com/mrjain/891cc88809697fb6a9c8872942fa8873#file-hello_world-py
import sys, getopt

# Add the pandas library
import pandas as pd
import pandas_datareader.data as web
import psycopg2 as pg
import pandas.io.sql as psql

# Add the plotting library to create graphs
import matplotlib.pyplot as plt
from matplotlib import style
import matplotlib

# Display graphs inline
# this is throwing an error for some reason
#%matplotlib inline 

# Variables
Start = ['2017-01-01']
End = ['2017-06-30']

def run(ccy,pair,Cash,Days):
    print "Running with Currency Pair {0}/{1}, Cash {2} and {3} Days".format(ccy,pair,Cash,Days)

    # get connected to the database and construct the SQL
    connection = pg.connect("dbname=crypto_data user=postgres")
    if ccy == 'BTC':
        sql = "SELECT time as date, opening_price as open, highest_price as high, lowest_price as low, closing_price as close, volume_btc as volume from btc_prices where closing_price > 0 and volume_btc > 0 and time > '2016-01-01' and currency_code = '{0}' order by time asc;".format(pair)
    else:
        sql = "SELECT time as date, opening_price as open, highest_price as high, lowest_price as low, closing_price as close, volume_btc as volume from crypto_prices where closing_price > 0 and volume_btc > 0 and time > '2016-01-01' and currency_code = '{0}' and currency_pair='{1}' order by time asc;".format(ccy,pair)
    print sql
    # The dataframe which contains the data which was received from the database
    df = psql.read_sql(sql, connection)


    # Add some columns to the dataframe
    columnName = "DMA"+str(Days)
    df[columnName] = df['close'].rolling(window=Days).mean()
    df['Action'] = 0.00
    df['Position'] = 0.00
    df['NumShares'] = 0
    df['NetExposure'] = 0.00
    df['GrossExposure'] = 0.00
    df['MTM'] = 0.00
    
    # Generate Trading Action and Positions (Buy=1 , Sell=-1, Do nothing=0)
    for s, row in df.iterrows():
        if row[columnName] > row['close'] and df.loc[s-1, columnName] < df.loc[s-1, 'close'] and row[columnName] != 0:
            df.loc[s, 'Action'] = -1
            df.loc[s, 'Position'] = -1 
        elif row[columnName] < row['close'] and df.loc[s-1, columnName] > df.loc[s-1, 'close'] and row[columnName] != 0:
            df.loc[s, 'Action'] = 1
            df.loc[s, 'Position'] = 1
        else:
            df.loc[s, 'Action'] = 0
        df.loc[s, 'Position'] = df.iloc[s-1, 8]

    # Calculate number of shares to go long or go short
    # Column 8 is Position, Column 9 is NumShares
    for x, row in df.iterrows():
        if df.loc[x, 'Position'] == df.iloc[x-1, 8]:
            df.loc[x, 'NumShares'] = df.iloc[x-1, 9]
        else:
            df.loc[x, 'NumShares'] = Cash/(df.loc[x, 'close']*df.loc[x, 'Position'])

    # Calculate Net Exposure and Gross Exposure
    df['NetExposure'] = df.NumShares * df.close
    df['GrossExposure'] = df.NumShares * df.close
    df['GrossExposure'] = df['GrossExposure'].abs()

    # Calculate Mark to Market (MTM)
    # Column 8 is Position, Column 10 is NetExposure
    for m, row in df.iterrows():
        if df.loc[m, 'Position'] == df.iloc[m-1, 8]:
            df.loc[m, 'MTM'] = df.loc[m, 'NetExposure'] - df.iloc[m-1, 10]
        else:
            df.loc[m, 'MTM'] = df.iloc[m-1, 10]*(df.loc[m, 'close']/df.iloc[m-1, 3]-1)  

    # Print the contents of the dataframe
    print df

    # Plot the chart to see the daily close and the DMA
    df[['close',columnName]].plot(grid=True,figsize=(16,5))
    plt.show()


def main(argv):
    Days = 10
    Cash = 1000
    ccy = 'BTC'
    pair = 'USD'
    try:
        opts, args = getopt.getopt(sys.argv[1:],"c:p:d:",["ccy=", "days=", "pair=", "cash="])
    except getopt.GetoptError:
        print str(sys.argv[0]) + ' --ccy|-c <currency> --pair|-p <currency> --days|-d <number of days> --cash <invest amount>'
        sys.exit(2)
    for opt, arg in opts:
        if opt in ('-h', '--help'):
            print str(sys.argv[0]) + ' --ccy|-c <currency> --pair|-p <currency> --days|-d <number of days> --cash <invest amount>'
        elif opt in ("-c", "--ccy"):
            ccy = arg.upper()
        elif opt in ("-d", "--days"):
            Days = int(arg)
        elif opt in ("--cash"):
            Cash = arg
        elif opt in ("-p", "--pair"):
            pair = arg.upper()
    run(ccy,pair,Cash,Days)


if __name__ == "__main__":
   main(sys.argv[1:])

• Updated Manish's script to work with TimeScale DB on Postgres with Cryptocurrenies. Not all that useful but a nice practice script to play with Postgrers/Timescale, Python, Pandas and look for some patterns in BTC and ETH