#Andrew Shafer's CoinTrackingCSVConverter.py
#Contact shaferandrew@gmail.com
# Program flow:
# Load the input CSV file
# Convert the loaded data into readable crypto csv transactions
# Write the output files

bSeparateBuys = False # Set to True if this is your first time importing to Money
bLimitDates = True # Set to True to limit output to the last iDatesAgo
iDatesAgo = 30

# importing csv module
import csv
from datetime import datetime, timedelta
 
# csv file name
filein = "CoinTracking.csv"

# initializing the fields and rows list
fields = []
rowsin = []

# reading csv file
with open(filein, 'rb') as csvfile:
    # creating a csv reader object
    csvreader = csv.reader(csvfile)

    # extracting field names through first row
    ifields = csvreader.next()
    ifields[0] = "Type" #fixes an import problem with the first field
 
    # extracting each data row one by one
    for row in csvreader:
        rowsin.append(row)
 
    # show total number of rows
    print("Total no. of rows read: %d"%(csvreader.line_num))
 
# printing the field names
#print('Field names input are:' + ', '.join(field for field in ifields))

#  printing first 5 rows
##print('\nFirst 5 rows in are:\n')
##for row in rowsin[:5]:
##    # parsing each column of a row
##    for col in row:
##        print("%2s"%col),
##    print('\n')

# assign input columns (ic) for easier reference
#Type, Buy, Cur., Buy value in USD, Sell, Cur., Sell value in USD, Fee, Cur., Exchange, Imported From, Trade Group, Comment, Trade ID, Trade Date
icType = ifields.index("Type") #Type
icBuyAmt = ifields.index("Buy") #Buy
icBuyCur = ifields.index("Cur.") #Cur.
icBuyUSD = ifields.index("Buy value in USD") #Buy value in USD
icSellAmt = ifields.index("Sell") #Sell
icSellCur = ifields.index("Cur.",icSellAmt) #Cur. (skip first "Cur.")
icSellUSD = ifields.index("Sell value in USD") #Sell value in USD
icFee = ifields.index("Fee") #Fee
icFeeCur = ifields.index("Cur.",icFee) #Cur. (skip first and second "Cur.")
icExchange = ifields.index("Exchange") #Exchange
#icImportSrc = ifields.index("Imported From") #Imported From #not used yet
#icTradeGp = ifields.index("Trade Group") #Trade Group #not used yet
icComment = ifields.index("Comment") #Comment
icTradeID = ifields.index("Trade ID") #Trade ID
icTradeDate = ifields.index("Trade Date") #Trade Date

# Change account names, as desired.
for row in rowsin:
    # Combine all Coinbase/GDAX/Coinbase Pro accounts as one.
    if row[icExchange] == "GDAX" or row[icExchange] == "Coinbase Pro": 
        row[icExchange] = "Coinbase"
##for row in rowsin:
##    row[icExchange] = "default"


# assign output columns (oc) for easier reference
ofields= ["Date", "Action", "Ticker", "Units", "Price", "Fees", "Total", "Memo", "ID", "Currency"]
ocCount = 10 #total number of output columns
ocDate = 0
ocAction = 1
ocTicker = 2
ocUnits = 3
ocPrice = 4
ocFees = 5
ocTotal = 6
ocMemo = 7
ocID = 8
ocCurrency = 9

# setup accounts, blank entries have an account of ""
accounts = set()
for row in rowsin:
    accounts.add(row[icExchange])
accounts = list(accounts)
#print(accounts)

accounttable = []
for account in accounts:
    accounttable.append([])

CutoffDate = datetime.now() - timedelta(days=iDatesAgo)

## Do stuff here...
for row in rowsin: #i=input, ic=input column; o=outpout; oc=output column
    workingrow = range(ocCount)
    
    #0 Date
    date = datetime.strptime(row[icTradeDate], '%d.%m.%Y %H:%M')
    if bLimitDates and date < CutoffDate:
        continue #skips this row and continues to the next input line
    workingrow[ocDate] = date.strftime('%m/%d/%Y')

    #1 Action Inputs:(Trade, Deposit, Withdrawal, Income, Spend)
    # Outputs: BUY, SELL, DEPOSIT, WITHDRAWAL, TRANSFER,
    #2 Ticker
    #workingrow[ocTicker] = row[icBuyCur]
    #3 Units
    #workingrow[ocUnits] = row[icBuyAmt]
    #4 Price
    #workingrow[ocPrice] = row[icSellAmt]
    #5 Fees
    #workingrow[ocFees] = row[icFee]
    #6 Total
    #workingrow[ocTotal] = row[icSellAmt]
    iType = row[icType] # Type
    iBuyAmt = row[icBuyAmt] #Buy
    iBuyCur = row[icBuyCur] #Cur.
    iBuyUSD = row[icBuyUSD] #Buy value in USD
    if iBuyCur == "":
        iBuyAmt = 0 #remove "-"
    iSellAmt = row[icSellAmt] #Sell
    iSellCur = row[icSellCur] #Cur.
    iSellUSD = row[icSellUSD] #Sell value in USD
    if iSellCur == "":
        iSellAmt = 0 #remove "-"
    iFee = row[icFee] #Fee
    iFeeCur = row[icFeeCur] #Cur.

    #set fee to be calibrated in USD
    if iFeeCur == "":
        iFee = 0 # nothing
    elif iFeeCur == iBuyCur:
        iFee = format(float(iBuyUSD)/float(iBuyAmt)*float(iFee), '.2f')
    elif iFeeCur == iSellCur:
        iFee = format(float(iSellUSD)/float(iSellAmt)*float(iFee), '.2f')
    else:
        print("Non-involved fee currency found at: " + str(rowsin.index(row)))
        
 
    #process different types
    if iType == "Deposit" and iBuyCur == "USD": #Deposit USD
        workingrow[ocAction] = "Deposit"
        workingrow[ocTicker] = "USD"
        workingrow[ocUnits] = iBuyAmt
        workingrow[ocPrice] = ""
        workingrow[ocFees] = ""
        workingrow[ocTotal] = iBuyAmt
    elif iType == "Withdrawal" and iSellCur == "USD": #Withdrawal USD
        workingrow[ocAction] = "Withdrawal"
        workingrow[ocTicker] = "USD"
        workingrow[ocUnits] = iSellAmt
        workingrow[ocPrice] = ""
        workingrow[ocFees] = ""
        workingrow[ocTotal] = iSellAmt
    elif iType == "Deposit" or iType == "Withdrawal": #Transfer Crypto
        print("Manual crypto transfer found at: " + str(rowsin.index(row)))
        workingrow[ocAction] = "Transfer"
        if iType == "Deposit": #Deposit
            workingrow[ocTicker] = iBuyCur
            workingrow[ocUnits] = iBuyAmt
            workingrow[ocPrice] = format(float(iBuyUSD)/float(iBuyAmt), '.6f')
            workingrow[ocFees] = ""
            workingrow[ocTotal] = iBuyUSD
        else: #Withdrawal
            workingrow[ocTicker] = iSellCur
            workingrow[ocUnits] = "-" + iSellAmt
            workingrow[ocPrice] = format(float(iSellUSD)/float(iSellAmt), '.6f')
            workingrow[ocFees] = ""
            workingrow[ocTotal] = "-" + iSellUSD

    elif iType == "Trade": #Trade for USD/Crypto or Crypto/Crypto
        if iBuyCur == "USD": #Sell crypto
            workingrow[ocAction] = "Sell"
            workingrow[ocTicker] = iSellCur
            workingrow[ocUnits] = iSellAmt
            workingrow[ocPrice] = format((float(iBuyAmt)+float(iFee))/float(iSellAmt), '.6f')
            workingrow[ocFees] = iFee
            workingrow[ocTotal] = iBuyAmt
        elif iSellCur == "USD": #Buy crypto
            workingrow[ocAction] = "Buy"
            workingrow[ocTicker] = iBuyCur
            workingrow[ocUnits] = iBuyAmt
            workingrow[ocPrice] = format((float(iSellAmt)-float(iFee))/float(iBuyAmt), '.6f')
            workingrow[ocFees] = iFee
            workingrow[ocTotal] = iSellAmt
        else: # a crypto for crypto trade, make a bonus row to "sell" transaction
            workingrow[ocAction] = "Buy"
            workingrow[ocTicker] = iBuyCur
            workingrow[ocUnits] = iBuyAmt
            workingrow[ocPrice] = format((float(iSellUSD)-float(iFee))/float(iBuyAmt), '.6f')
            workingrow[ocFees] = iFee
            workingrow[ocTotal] = iSellUSD #set to be neutral with sell amt

            bonusrow = range(ocCount) # only needed for cypto to crypto, creates a second entry
            bonusrow[ocDate] = workingrow[ocDate]
            bonusrow[ocAction] = "Sell"
            bonusrow[ocTicker] = iSellCur
            bonusrow[ocUnits] = iSellAmt
            bonusrow[ocPrice] = format(float(iSellUSD)/float(iSellAmt), '.6f')
            bonusrow[ocFees] = 0
            bonusrow[ocTotal] = iSellUSD
            bonusrow[ocMemo] = row[icComment]
            bonusrow[ocID] = "XXXC2CTradeXXX" + row[icTradeID]
            bonusrow[ocCurrency] = "USD"
            accounttable[accounts.index(row[icExchange])].append(bonusrow)
    elif iType == "Income": #record deposit and buy
        workingrow[ocAction] = "Deposit"
        workingrow[ocTicker] = "USD"
        workingrow[ocUnits] = iBuyUSD
        workingrow[ocPrice] = ""
        workingrow[ocFees] = ""
        workingrow[ocTotal] = iBuyUSD
        if iBuyCur != "USD":
            bonusrow = range(ocCount)
            bonusrow[ocDate] = workingrow[ocDate]
            bonusrow[ocAction] = "Buy"
            bonusrow[ocTicker] = iBuyCur
            bonusrow[ocUnits] = iBuyAmt
            bonusrow[ocPrice] = format(float(iBuyUSD)/float(iBuyAmt), '.6f')
            bonusrow[ocFees] = 0
            bonusrow[ocTotal] = iBuyUSD
            bonusrow[ocMemo] = row[icComment]
            bonusrow[ocID] = "XXXCryptoIncomeXXX" + row[icTradeID]
            bonusrow[ocCurrency] = "USD"
            accounttable[accounts.index(row[icExchange])].append(bonusrow)
    elif iType == "Spend": #record sell and withdrawal
        workingrow[ocAction] = "Withdrawal"
        workingrow[ocTicker] = "USD"
        workingrow[ocUnits] = iSellUSD
        workingrow[ocPrice] = ""
        workingrow[ocFees] = ""
        workingrow[ocTotal] = iSellUSD
        if iBuyCur != "USD":
            bonusrow = range(ocCount)
            bonusrow[ocDate] = workingrow[ocDate]
            bonusrow[ocAction] = "Sell"
            bonusrow[ocTicker] = iSellCur
            bonusrow[ocUnits] = iSellAmt
            bonusrow[ocPrice] = format(float(iSellUSD)/float(iSellAmt), '.6f')
            bonusrow[ocFees] = 0
            bonusrow[ocTotal] = iSellUSD
            bonusrow[ocMemo] = row[icComment]
            bonusrow[ocID] = "XXXCryptoSpendXXX" + row[icTradeID]
            bonusrow[ocCurrency] = "USD"
            accounttable[accounts.index(row[icExchange])].append(bonusrow)
    else:
        print("Unrecognized type entry: " + str(rowsin.index(row)))

    #7 Memo
    workingrow[ocMemo] = row[icComment]

    #8 Transaction ID (should be unique per transaction!!!)
    workingrow[ocID] = row[icTradeID]

    #9 Currency
    workingrow[ocCurrency] = "USD"

    accounttable[accounts.index(row[icExchange])].append(workingrow)

#  printing first 5 rows
##print('\nFirst 5 rows out are:\n')
##for row in rowsout[:5]:
##    # parsing each column of a row
##    for col in row:
##        print("%2s"%col),
##    print('\n')

# writing to csv file
if bSeparateBuys: #separate buy files
    for account in accounts:
        buys = []
        allelse = []
        
        for row in accounttable[accounts.index(account)]:
            if row[ocAction] == "Buy":
                buys.append(row)
            else:
                allelse.append(row)

        if len(buys) > 0:
            with open("BUYS" + account + ".csv", 'wb') as csvfile:
                # creating a csv writer object
                csvwriter = csv.writer(csvfile)
                 
                # writing the fields
                csvwriter.writerow(ofields)
                 
                # writing the data rows
                csvwriter.writerows(buys)
                
                # get total number of rows
                print("Wrote " + str(len(buys)) + " buys for account: " + str(account))

        with open("OTHERS" + account + ".csv", 'wb') as csvfile:
            # creating a csv writer object
            csvwriter = csv.writer(csvfile)
             
            # writing the fields
            csvwriter.writerow(ofields)
             
            # writing the data rows
            csvwriter.writerows(allelse)
            
            # get total number of rows
            print("Wrote " + str(len(allelse)) + " other entries for account: " + str(account))
        
else: #combined files
    for account in accounts: 
        with open(account + ".csv", 'wb') as csvfile:
            # creating a csv writer object
            csvwriter = csv.writer(csvfile)
             
            # writing the fields
            csvwriter.writerow(ofields)
             
            # writing the data rows
            csvwriter.writerows(accounttable[accounts.index(account)])
            
            # get total number of rows
            print("Wrote " + str(len((accounttable[accounts.index(account)]))) + " entries for account: " + str(account))


# mark completion
print("Script complete")