// THIS SCRIPT IS FOR EDUCATIONAL PURPOSES ONLY AND ANYONE
// WHO COPIES IT IS TAKING FULL RESPONSIBILITY FOR ITS USE.
// THE ORIGINAL AUTHOR, J. ARTHUR SQUIERS, DISCLAIMS ANY LIABILITY, LOSS OR RISK
// PERSONAL OR OTHERWISE, WHICH IS INCURRED AS A CONSEQUENCE OF THE USE
// OF THIS SCRIPT.
// NOTE: You need to have a csv file uploaded somewhere on your google drive,named firebyarthur.csv in order to import.
// NOTE: The following column headers SHOULD be present in your csv file: Symbol, / Delta, NetLiq, Cost, DTE, Strike Price, Quantity
var csvFileName = "firebyarthur.csv";
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Trading Integrations')
.addItem('Load Positions CSV','importCSVFromGoogleDrive')
.addToUi();
}
//Create spreadsheet column numbers
var symbolSheet = 1;
var statusSheet = 2;
var expireDateSheet = 3;
var originalCreditSheet = 4;
var costSheet = 5;
var netLiqSheet = 6;
var plSheet = 7;
var plPercentSheet = 8;
var deltaSheet = 9;
var adjust1Sheet = 10;
var adjust2Sheet = 11;
var adjust3Sheet = 12;
var adjust4Sheet = 13;
var adjust5Sheet = 14;
var targetNetLiqSheet = 15;
var quantityNumSheet = 16;
var targetMarketPriceSheet = 17;
var openDateSheet = 18;
var openDaysInTradeSheet = 19;
var startingDTESheet = 20;
var biggestDeltaOpenSheet = 21;
var closedDateSheet = 22;
var daysInTradeSheet = 23;
function createOpenHeaders(sheet) {
sheet.getRange(1,symbolSheet).setValue("Ticker");
sheet.getRange(1,statusSheet).setValue("Status");
sheet.getRange(1,expireDateSheet).setValue("DTE");
sheet.getRange(1,originalCreditSheet).setValue("Original Credit");
sheet.getRange(1,costSheet).setValue("Total Cost Basis");
sheet.getRange(1,netLiqSheet).setValue("Open Net Liq");
sheet.getRange(1,plSheet).setValue("Position P/L");
sheet.getRange(1,plPercentSheet).setValue("Position P/L Percent");
sheet.getRange(1,deltaSheet).setValue("Biggest Delta");
sheet.getRange(1,adjust1Sheet).setValue("1st Adj Credit");
sheet.getRange(1,adjust2Sheet).setValue("2nd Adj Credit");
sheet.getRange(1,adjust3Sheet).setValue("3rd Adj Credit");
sheet.getRange(1,adjust4Sheet).setValue("4th Adj Credit");
sheet.getRange(1,adjust5Sheet).setValue("5th Adj Credit");
sheet.getRange(1,targetNetLiqSheet).setValue("Target Net Liq");
sheet.getRange(1,quantityNumSheet).setValue("Quantity");
sheet.getRange(1,targetMarketPriceSheet).setValue("Target Market Price");
sheet.getRange(1,openDateSheet).setValue("Open Date");
sheet.getRange(1,openDaysInTradeSheet).setValue("Days In Trade");
sheet.getRange(1,startingDTESheet).setValue("Starting DTE");
sheet.getRange(1,biggestDeltaOpenSheet).setValue("Biggest Delta On Open");
}
function createClosedHeaders(sheet) {
sheet.getRange(1,symbolSheet).setValue("Ticker");
sheet.getRange(1,statusSheet).setValue("Status");
sheet.getRange(1,expireDateSheet).setValue("DTE");
sheet.getRange(1,originalCreditSheet).setValue("Original Credit");
sheet.getRange(1,costSheet).setValue("Total Cost Basis");
sheet.getRange(1,netLiqSheet).setValue("Open Net Liq");
sheet.getRange(1,plSheet).setValue("Position P/L");
sheet.getRange(1,plPercentSheet).setValue("Position P/L Percent");
sheet.getRange(1,deltaSheet).setValue("Biggest Delta");
sheet.getRange(1,adjust1Sheet).setValue("1st Adj Credit");
sheet.getRange(1,adjust2Sheet).setValue("2nd Adj Credit");
sheet.getRange(1,adjust3Sheet).setValue("3rd Adj Credit");
sheet.getRange(1,adjust4Sheet).setValue("4th Adj Credit");
sheet.getRange(1,adjust5Sheet).setValue("5th Adj Credit");
sheet.getRange(1,targetNetLiqSheet).setValue("Target Net Liq");
sheet.getRange(1,quantityNumSheet).setValue("Quantity");
sheet.getRange(1,targetMarketPriceSheet).setValue("Target Market Price");
sheet.getRange(1,openDateSheet).setValue("Open Date");
sheet.getRange(1,openDaysInTradeSheet).setValue("Days In Trade");
sheet.getRange(1,startingDTESheet).setValue("Starting DTE");
sheet.getRange(1,biggestDeltaOpenSheet).setValue("Biggest Delta On Open");
sheet.getRange(1, closedDateSheet).setValue("Closed Date");
sheet.getRange(1, daysInTradeSheet).setValue("Days In Trade");
}
function importCSVFromGoogleDrive() {
if (!DriveApp.getFilesByName(csvFileName).hasNext()) throw new Error("Please make sure a CSV file named "+csvFileName+" has been saved in your Google drive.");
var file = DriveApp.getFilesByName(csvFileName).next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open Positions");
var closedSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Closed Positions");
if (sheet == null) sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Open Positions");
if (closedSheet == null) closedSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Closed Positions");
var netLiqHash = {};
var costHash = {};
var expireHash = {};
var spreadsheetContainHash = {};
var biggestDeltaHash = {};
var quantityNumHash = {};
//Create the cvs format variables
var symbolColumn = 1;
var strikeColumn = 6;
var deltaColumn = 16;
var netLiqColumn = 15;
var costColumn = 13;
var expireColumn = 5;
var quantityColumn = 3;
//End create file format
//End create spreadhseet column numbers
checkCSVFormat();
//Loop through csv and build hashes
for ( var i=1, lenCsv=csvData.length; i<lenCsv; i++ ) {
Logger.log(csvData[i][symbolColumn]);
var typeArray = ((csvData[i][symbolColumn]).split(" "));
var symbol = typeArray[0];
var tmpType = typeArray[typeArray.length-1];
var strike = csvData[i][strikeColumn];
var quantity = csvData[i][quantityColumn];
var tmpNL = netLiqHash[symbol];
if (tmpNL == null) tmpNL = 0;
var tmpCost = costHash[symbol];
if (tmpCost == null) tmpCost = 0;
//get biggest delta
var tmpDelta = biggestDeltaHash[symbol];
var deltaStr = csvData[i][deltaColumn];
//Check if delta is a valid number
if(!isNaN(parseFloat(deltaStr)) && isFinite(deltaStr)) {
var newDelta = Math.abs(csvData[i][deltaColumn])*100;
if ( Math.abs(quantity) > 0 ) { // If there is still a closed position in the cvs, we don't want it's values muddling us.
if (tmpDelta != null) {
if (newDelta > tmpDelta) biggestDeltaHash[symbol] = newDelta;
}
else {
biggestDeltaHash[symbol] = newDelta;
}
}
//end getting biggest delta
} //end check if delta is valid number
var quantityNum = Math.abs(quantity);
if (quantityNum > 0) quantityNumHash[symbol] = quantityNum;
tmpNL = tmpNL + parseFloat((csvData[i][netLiqColumn]).replace(",",""));
tmpCost = tmpCost + parseFloat((csvData[i][costColumn]).replace(",",""));
var tmpExpire = csvData[i][expireColumn];
tmpExpire = tmpExpire.toString().replace("d","");
var dte = Math.abs(tmpExpire);
var oldDTE = expireHash[symbol];
if (oldDTE != null && oldDTE < dte) dte = oldDTE;
netLiqHash[symbol] = tmpNL;
costHash[symbol] = tmpCost;
if (quantityNum > 0) expireHash[symbol] = dte;
}
//Loop through all spreadsheet rows, find open positions and update
//values
for (var i=2, rowCount=sheet.getLastRow(); i<= rowCount; i++) {
var tmpStatus = sheet.getRange(i, statusSheet);
var tmpSymbol = sheet.getRange(i,symbolSheet);
var tmpStatusVal = tmpStatus.getValue();
var tmpSymbolVal = tmpSymbol.getValue();
//If the position is open, we can see if we have a net liq
if (tmpStatusVal == "Open") {
var tmpNetLiq = Math.abs(netLiqHash[tmpSymbolVal]);
if (!isNaN(tmpNetLiq) && tmpNetLiq != null && tmpNetLiq != 0) {
var tmpNetLiqCell = sheet.getRange(i,netLiqSheet);
tmpNetLiqCell.setValue(tmpNetLiq);
}
//if there is no net liq or it's zero, we'll mark this position closed
if (isNaN(tmpNetLiq) || tmpNetLiq == null || tmpNetLiq == 0) tmpStatus.setValue("Closed")
else { //If we are setting this to closed, there is no need to fill in the rest of the data because
//it will just mess up the history.
// var tmpDeltaCell = sheet.getRange(i,deltaSheet);
var biggestHash = biggestDeltaHash[tmpSymbolVal];
var expireValue = expireHash[tmpSymbolVal];
var expireCell = sheet.getRange(i,expireDateSheet);
expireCell.setValue(expireValue);
var quantityValue = quantityNumHash[tmpSymbolVal];
var quantityNumCell = sheet.getRange(i,quantityNumSheet);
quantityNumCell.setValue(quantityValue);
}
spreadsheetContainHash[tmpSymbolVal] = tmpSymbolVal;
}
}
//Now loop through all symbols from import and add new rows for
//the ones that don't have open rows in the spreadsheet
for (var key in netLiqHash) {
if (spreadsheetContainHash[key] == null) {
//Make sure it's not just a closed position
if (costHash[key] > 0) {
var lastRow = sheet.getLastRow();
if (lastRow == 0) {
createOpenHeaders(sheet);
lastRow = 1;
}
sheet.insertRowAfter(lastRow);
lastRow++;
sheet.getRange(lastRow,symbolSheet).setValue(key);
sheet.getRange(lastRow,statusSheet).setValue("Open");
sheet.getRange(lastRow,expireDateSheet).setValue(expireHash[key]);
sheet.getRange(lastRow,originalCreditSheet).setValue(costHash[key]);
sheet.getRange(lastRow,costSheet).setFormula("=D"+lastRow+"+J"+lastRow+"+K"+lastRow+"+L"+lastRow+"+M"+lastRow+"+N"+lastRow);
sheet.getRange(lastRow,netLiqSheet).setValue(Math.abs(netLiqHash[key]));
sheet.getRange(lastRow,plSheet).setFormula("=E"+lastRow+"-F"+lastRow);
sheet.getRange(lastRow,plPercentSheet).setNumberFormat("#.##%");
sheet.getRange(lastRow,plPercentSheet).setFormula("=G"+lastRow+"/D"+lastRow);
sheet.getRange(lastRow,deltaSheet).setValue(biggestDeltaHash[key]);
sheet.getRange(lastRow,quantityNumSheet).setValue(quantityNumHash[key]);
sheet.getRange(lastRow,targetNetLiqSheet).setFormula("=((D"+lastRow+"/2)-E"+lastRow+")*-1");
sheet.getRange(lastRow,targetMarketPriceSheet).setFormula("=O"+lastRow+"/P"+lastRow);
sheet.getRange(lastRow,openDateSheet).setValue(Utilities.formatDate(new Date(), "CST", "MM/dd/yyyy"));
sheet.getRange(lastRow,openDaysInTradeSheet).setFormula("=DATEDIF(R"+lastRow+",TODAY(),\"D\")");
//column 23 left blank for IFTTT data
sheet.getRange(lastRow,startingDTESheet).setValue(expireHash[key]);
sheet.getRange(lastRow,biggestDeltaOpenSheet).setValue(biggestDeltaHash[key]);
}
}
}
var finalRow = sheet.getLastRow();
if (finalRow == 0) finalRow == 2;
if (closedSheet.getLastRow() == 0) {
createClosedHeaders(closedSheet);
}
for (var i=2; i<= finalRow; i++) {
var tmpStatus = sheet.getRange(i, statusSheet);
if (tmpStatus.getValue() == "Closed") {
var targetRange = closedSheet.getRange(closedSheet.getLastRow() + 1, 1);
sheet.getRange(tmpStatus.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
var closedDateRange = closedSheet.getRange(targetRange.getRow(),closedDateSheet);
closedDateRange.setValue(Utilities.formatDate(new Date(), "CST", "MM/dd/yyyy"));
closedSheet.getRange(targetRange.getRow(), daysInTradeSheet).setFormula("=DATEDIF(R"+targetRange.getRow()+",V"+targetRange.getRow()+",\"D\")");
sheet.deleteRow(tmpStatus.getRow());
//Since we deleted one, we need to go back one row in the loop
i--;
}
}
//Internal checkCSVFormat option
function checkCSVFormat() {
//Create the cvs format variables
var foundSymbol = false;
var foundDelta = false;
var foundNL = false;
var foundCost = false;
var foundExpDate = false;
var foundStrike = false;
var foundQuantity = false;
for ( var i=0, lenCsv=csvData[0].length; i<lenCsv; i++ ) {
var columnHeader = csvData[0][i];
if (columnHeader == "Symbol") {
symbolColumn = i;
foundSymbol = true;
}
else if (columnHeader == "/ Delta") {
deltaColumn = i;
foundDelta = true;
}
else if (columnHeader == "NetLiq") {
netLiqColumn = i;
foundNL = true;
}
else if (columnHeader == "Cost") {
costColumn = i;
foundCost = true;
}
else if (columnHeader == "DTE") {
expireColumn = i;
foundExpDate = true;
}
else if (columnHeader == "Strike Price") {
strikeColumn = i;
foundStrike = true;
}
else if (columnHeader == "Quantity") {
quantityColumn = i;
foundQuantity = true;
}
}
if (!foundSymbol) throw new Error("Symbol column not found in CSV import");
if (!foundDelta) throw new Error("Delta column not found in CSV import");
if (!foundNL) throw new Error("Net Liq column not found in CSV import");
if (!foundCost) throw new Error("Cost column not found in CSV import");
if (!foundExpDate) throw new Error("Expire Date column not found in CSV import");
if (!foundStrike) throw new Error("Strike Price column not found in CSV import");
if (!foundQuantity) throw new Error("Quantity column not found in CSV import");
//End create file format
}
}
Advertisements
