"function updateOpenPortfolio() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var…
- User
"function updateOpenPortfolio() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Trade Log"); if (!sheet) { Logger.log("Trade Log sheet not found. Please check the sheet name."); return; }
var portfolioSheet = ss.getSheetByName("Portfolio"); if (!portfolioSheet) { portfolioSheet = ss.insertSheet("Portfolio"); }
// Set headers if they are not already set var headerRange = portfolioSheet.getRange('A3:H3'); var currentHeaders = headerRange.getValues()[0]; var newHeaders = ["SN.", "Ticker", "Type", "# Shares", "Avg. Buy @", "Asset Class", "Broker", "User"];
// Only set headers if they are different if (JSON.stringify(currentHeaders) !== JSON.stringify(newHeaders)) { headerRange.setValues([newHeaders]); }
// Clear existing data in columns A to H starting from row 4 var lastRow = portfolioSheet.getLastRow(); if (lastRow > 3) { portfolioSheet.getRange(4, 1, lastRow - 3, 8).clearContent(); // Clear only columns A to H }
// Batch read data from the Trade Log var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()); var data = dataRange.getValues();
if (data.length === 0) { Logger.log("No data found in Trade Log sheet."); return; }
// Process header row var headers = data[0].map(header => header.toString().trim().toLowerCase()); var expectedHeaders = { "type": ["type"], "date": ["date"], "security": ["security"], "action": ["action"], "quantity": ["quantity"], "@price": ["@price", "price"], "fees": ["fees"], "asset class": ["asset class"], "broker": ["broker"], "user": ["user"] };
var headerIndices = {}; var missingHeaders = [];
for (var key in expectedHeaders) { var found = false; for (var i = 0; i < expectedHeaders[key].length; i++) { var possibleHeader = expectedHeaders[key][i].toLowerCase(); var idx = headers.indexOf(possibleHeader); if (idx !== -1) { headerIndices[key] = idx; found = true; break; } } if (!found) { missingHeaders.push(key); } }
if (missingHeaders.length > 0) { Logger.log("Missing Headers:", missingHeaders.join(", ")); throw new Error("One or more required columns are missing in Trade Log sheet."); }
var positionsMap = new Map();
function generateTrade(quantity, price, fees, action, assetClass, broker, user, date, tradeType) { return { shares: quantity, price: price, fees: fees, action: action, assetClass: assetClass, broker: broker, user: user, date: date, type: tradeType }; }
for (var i = 1; i < data.length; i++) { var row = data[i];
// Check if the row is empty if (row.every(cell => cell === "")) { continue; // Skip empty rows } var type = (row[headerIndices["type"]] || "").toString().trim().toLowerCase(); var dateCell = row[headerIndices["date"]]; var date = dateCell instanceof Date ? dateCell : new Date(dateCell); if (isNaN(date.getTime())) { Logger.log(Invalid date in row ${i + 2}: ${dateCell}); continue; } var action = (row[headerIndices["action"]] || "").toString().trim().toLowerCase(); var ticker = (row[headerIndices["security"]] || "").toString().trim(); var quantity = parseFloat(row[headerIndices["quantity"]]) || 0; var price = parseFloat(row[headerIndices["@price"]]) || 0.0; var fees = parseFloat(row[headerIndices["fees"]]) || 0.0; var assetClass = (row[headerIndices["asset class"]] || "Unknown").toString().trim(); var broker = (row[headerIndices["broker"]] || "Unknown").toString().trim(); var user = (row[headerIndices["user"]] || "Unknown").toString().trim(); var tradeType = ""; if (type === 'delivery') { if (action === 'buy') { tradeType = 'Long'; let key = ${ticker}|${broker}|${user}|${assetClass}|${tradeType}; let trade = generateTrade(quantity, price, fees, action, assetClass, broker, user, date, tradeType); let activeTrades = positionsMap.get(key); if (!activeTrades) { positionsMap.set(key, [trade]); } else { activeTrades.push(trade); } } else if (action === 'sell') { tradeType = 'Long'; let key = ${ticker}|${broker}|${user}|${assetClass}|${tradeType}; let activeTrades = positionsMap.get(key); if (activeTrades) { let sharesToProcess = quantity; activeTrades.sort((a, b) => a.date - b.date); // FIFO let iTrade = 0; while (sharesToProcess > 0 && iTrade < activeTrades.length) { let trade = activeTrades[iTrade]; if (trade.shares <= sharesToProcess) { sharesToProcess -= trade.shares; activeTrades.splice(iTrade, 1); } else { trade.shares -= sharesToProcess; sharesToProcess = 0; } } if (activeTrades.length == 0) { positionsMap.delete(key); } } else { Logger.log(Warning: No positions found for key ${key} when processing sell action.); } } else if (action === 'sellshort') { tradeType = 'Short'; let key = ${ticker}|${broker}|${user}|${assetClass}|${tradeType}; let trade = generateTrade(quantity, price, fees, action, assetClass, broker, user, date, tradeType); let activeTrades = positionsMap.get(key); if (!activeTrades) { positionsMap.set(key, [trade]); } else { activeTrades.push(trade); } } else if (action === 'buytocover') { tradeType = 'Short'; let key = ${ticker}|${broker}|${user}|${assetClass}|${tradeType}; let activeTrades = positionsMap.get(key); if (activeTrades) { let sharesToProcess = quantity; activeTrades.sort((a, b) => a.date - b.date); // FIFO let iTrade = 0; while (sharesToProcess > 0 && iTrade < activeTrades.length) { let trade = activeTrades[iTrade]; if (trade.shares <= sharesToProcess) { sharesToProcess -= trade.shares; activeTrades.splice(iTrade, 1); } else { trade.shares -= sharesToProcess; sharesToProcess = 0; } } if (activeTrades.length == 0) { positionsMap.delete(key); } } else { Logger.log(Warning: No positions found for key ${key} when processing buytocover action.); } } } else if (type === 'corp. action' && action === 'split') { // Process stock splits let splitRatioStr = (row[headerIndices["quantity"]] || "").toString().trim(); let ratio = 1; if (splitRatioStr.includes(":")) { let ratioParts = splitRatioStr.split(':'); let numerator = parseFloat(ratioParts[0]); let denominator = parseFloat(ratioParts[1]); ratio = numerator / denominator; } else { ratio = parseFloat(splitRatioStr); } positionsMap.forEach((value, pKey) => { if (pKey.startsWith(${ticker}|)) { value.forEach(trade => { trade.shares *= ratio; trade.price /= ratio; }); } }); } else if (type === 'my action') { if (action === 'transferfrom') { // Handle transfer from one broker to another tradeType = 'Long'; // Assuming transfer applies to Long positions let key = ${ticker}|${broker}|${user}|${assetClass}|${tradeType}; let nextRow = data[i + 1]; if (nextRow) { let newBroker = (nextRow[headerIndices["broker"]] || "Unknown").toString().trim(); let newKey = ${ticker}|${newBroker}|${user}|${assetClass}|${tradeType}; let activeTrades = positionsMap.get(key); if (activeTrades) { let sharesToTransfer = quantity; let totalAvailableShares = activeTrades.reduce((sum, trade) => sum + trade.shares, 0); if (sharesToTransfer > totalAvailableShares) { Logger.log(Not enough shares to transfer for ${key}.); continue; } let transferredTrades = []; activeTrades.sort((a, b) => a.date - b.date); // FIFO while (sharesToTransfer > 0 && activeTrades.length > 0) { let trade = activeTrades[0]; if (trade.shares <= sharesToTransfer) { sharesToTransfer -= trade.shares; transferredTrades.push({ ...trade }); // Copy trade activeTrades.splice(0, 1); } else { let transferredTrade = { ...trade, shares: sharesToTransfer }; trade.shares -= sharesToTransfer; sharesToTransfer = 0; transferredTrades.push(transferredTrade); } } if (activeTrades.length == 0) { positionsMap.delete(key); } // Add transferred trades to new broker let newActiveTrades = positionsMap.get(newKey); if (!newActiveTrades) { positionsMap.set(newKey, transferredTrades); } else { newActiveTrades.push(...transferredTrades); } } else { Logger.log(No active trades found for key ${key} during transfer.); } } else { Logger.log(No next row found for transfer action at row ${i + 2}.); } } }} // End of loop processing
// Prepare data for output let returnArry = []; let sn = 1; let sortedKeys = Array.from(positionsMap.keys()).sort(); sortedKeys.forEach(key => { let value = positionsMap.get(key); let totalShares = 0; let totalCost = 0; let totalFees = 0; let avgPrice = 0; let [ticker, currentBroker, currentUser, currentAssetClass, longOrShort] = key.split('|'); value.forEach(trade => { totalShares += trade.shares; totalCost += trade.shares * trade.price; totalFees += trade.fees; }); avgPrice = totalShares > 0 ? (totalCost + totalFees) / totalShares : 0;
returnArry.push([ sn++, ticker, longOrShort, Number(totalShares.toFixed(4)), avgPrice.toFixed(2), currentAssetClass, currentBroker, currentUser ]);});
// Output to "Portfolio" sheet if (returnArry.length > 0) { portfolioSheet.getRange(4, 1, returnArry.length, returnArry[0].length).setValues(returnArry); portfolioSheet.getRange(4, 4, returnArry.length, 2).setNumberFormat('#,##0.00'); } else { Logger.log("No open positions to display."); }
// Record the last update date and time let now = new Date(); let formattedNow = Utilities.formatDate(now, "Asia/Kolkata", "dd-MMM-yyyy HH:mm:ss"); portfolioSheet.getRange('V1').setValue(formattedNow);
Logger.log("Open portfolio updated successfully."); }"
"function updateOpenPortfolio() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Trade Log"); if (!sheet) { Logger.log("Trade Log sheet not found. Please check the sheet name."); return; }
var portfolioSheet = ss.getSheetByName("Portfolio"); if (!portfolioSheet) { portfolioSheet = ss.insertSheet("Portfolio"); }
// Set headers if they are not already set var headerRange = portfolioSheet.getRange('A3:H3'); var currentHeaders = headerRange.getValues()[0]; var newHeaders = ["SN.", "Ticker", "Type", "# Shares", "Avg. Buy @", "Asset Class", "Broker", "User"];
// Only set headers if they are different if (JSON.stringify(currentHeaders) !== JSON.stringify(newHeaders)) { headerRange.setValues([newHeaders]); }
// Clear existing data in columns A to H starting from row 4 var lastRow = portfolioSheet.getLastRow(); if (lastRow > 3) { portfolioSheet.getRange(4, 1, lastRow - 3, 8).clearContent(); // Clear only columns A to H }
// Batch read data from the Trade Log var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()); var data = dataRange.getValues();
if (data.length === 0) { Logger.log("No data found in Trade Log sheet."); return; }
// Process header row var headers = data[0].map(header => header.toString().trim().toLowerCase()); var expectedHeaders = { "type": ["type"], "date": ["date"], "security": ["security"], "action": ["action"], "quantity": ["quantity"], "@price": ["@price", "price"], "fees": ["fees"], // Added Fees "asset class": ["asset class"], "broker": ["broker"], "user": ["user"] };
var headerIndices = {}; var missingHeaders = [];
for (var key in expectedHeaders) { var found = false; for (var i = 0; i < expectedHeaders[key].length; i++) { var possibleHeader = expectedHeaders[key][i].toLowerCase(); var idx = headers.indexOf(possibleHeader); if (idx !== -1) { headerIndices[key] = idx; found = true; break; } } if (!found) { missingHeaders.push(key); } }
if (missingHeaders.length > 0) { Logger.log("Missing Headers:", missingHeaders.join(", ")); throw new Error("One or more required columns are missing in Trade Log sheet."); }
var positionsMap = new Map(); var pendingTransfers = {};
function addPosition(ticker, assetClass, broker, user, position) { var key = {assetClass}|{user}; if (!positionsMap.has(key)) { positionsMap.set(key, []); } positionsMap.get(key).push(position); }
function removePositions(ticker, assetClass, broker, user, quantity) { var key = {assetClass}|{user}; var remainingQuantity = quantity; var removedPositions = [];
if (positionsMap.has(key)) { var positions = positionsMap.get(key); positions.sort((a, b) => a.date - b.date); // FIFO positionsMap.set(key, positions.filter(pos => { if (remainingQuantity <= 0) return true; if (pos.quantity <= remainingQuantity) { removedPositions.push({ ...pos }); remainingQuantity -= pos.quantity; return false; } else { var splitPos = { ...pos, quantity: remainingQuantity }; removedPositions.push(splitPos); pos.quantity -= remainingQuantity; remainingQuantity = 0; return true; } })); } else { // No matching positions found under this key Logger.log(Warning: No positions found for key ${key} when removing positions.); } return removedPositions;}
for (var i = 1; i < data.length; i++) { var row = data[i];
// Check if the row is empty if (row.every(cell => cell === "")) { continue; // Skip empty rows } var type = (row[headerIndices["type"]] || "").toString().trim().toLowerCase(); var dateCell = row[headerIndices["date"]]; var date; if (dateCell instanceof Date) { date = dateCell; } else { date = new Date(dateCell); } if (isNaN(date.getTime())) { Logger.log(Invalid date in row ${i+2}: ${dateCell}); continue; } var action = (row[headerIndices["action"]] || "").toString().trim().toLowerCase(); var ticker = (row[headerIndices["security"]] || "").toString().trim(); var quantity = parseFloat(row[headerIndices["quantity"]]) || 0; var price = parseFloat(row[headerIndices["@price"]]) || 0.0; var fees = parseFloat(row[headerIndices["fees"]]) || 0.0; // Extract Fees var assetClass = (row[headerIndices["asset class"]] || "").toString().trim().toLowerCase(); var broker = (row[headerIndices["broker"]] || "").toString().trim().toLowerCase(); var user = (row[headerIndices["user"]] || "").toString().trim().toLowerCase(); if (type === "delivery") { if (action === "buy" || action === "sellshort") { addPosition(ticker, assetClass, broker, user, { date: date, // Store the Date object quantity: quantity, price: price, fees: fees, // Include Fees tradeType: action === "buy" ? "Long" : "Short" }); } else if (action === "sell" || action === "buytocover") { removePositions(ticker, assetClass, broker, user, quantity); } } else if (type === "my action") { if (action === "transferfrom") { var transferKey = ${ticker}|${assetClass}|${quantity}|${date.getTime()}|${broker}|${user}; pendingTransfers[transferKey] = removePositions(ticker, assetClass, broker, user, quantity); } else if (action === "transferto") { var transferKey = ${ticker}|${assetClass}|${quantity}|${date.getTime()}; var matchingTransfer = Object.keys(pendingTransfers).find(key => key.startsWith(transferKey)); if (matchingTransfer) { pendingTransfers[matchingTransfer].forEach(pos => addPosition(ticker, assetClass, broker, user, pos) ); delete pendingTransfers[matchingTransfer]; } else { Logger.log(Warning: No matching transfer found for TransferTo action: ${ticker}, ${quantity}, ${date}); } } } else if (type === "corp. action" && action === "split") { // Handle stock splits var splitRatioStr = (row[headerIndices["quantity"]] || "").toString().trim(); var splitRatio = 1; if (splitRatioStr.includes(":")) { var parts = splitRatioStr.split(":"); if (parts.length == 2) { var numerator = parseFloat(parts[0]); var denominator = parseFloat(parts[1]); if (denominator !== 0) { splitRatio = numerator / denominator; } else { Logger.log(Invalid split ratio in row ${i + 2}: ${splitRatioStr}); continue; } } else { Logger.log(Invalid split ratio format in row ${i + 2}: ${splitRatioStr}); continue; } } else { splitRatio = parseFloat(splitRatioStr); if (isNaN(splitRatio) || splitRatio <= 0) { Logger.log(Invalid split ratio in row ${i + 2}: ${splitRatioStr}); continue; } } for (var key of positionsMap.keys()) { // Check if the key starts with the ticker if (key.split('|')[0] === ticker) { positionsMap.get(key).forEach(function (pos) { pos.quantity *= splitRatio; pos.price /= splitRatio; }); } } }}
// Prepare data for output var consolidatedPositions = {};
for (var key of positionsMap.keys()) { var [ticker, assetClass, broker, user] = key.split('|'); var totalQuantity = 0; var totalValue = 0; var totalFees = 0; // Initialize total fees
positionsMap.get(key).forEach(function (pos) { if (pos.quantity > 0) { totalQuantity += pos.quantity; totalValue += pos.quantity * pos.price; totalFees += pos.fees; // Accumulate fees } }); if (totalQuantity > 0) { var avgPrice = (totalValue + totalFees) / totalQuantity; // Include fees in avg price var consolidationKey = ${ticker}|${assetClass}|${broker}|${user}; consolidatedPositions[consolidationKey] = { ticker: ticker, type: positionsMap.get(key)[0].tradeType, quantity: totalQuantity, avgPrice: avgPrice, assetClass: assetClass.charAt(0).toUpperCase() + assetClass.slice(1), // Capitalize for display broker: broker.charAt(0).toUpperCase() + broker.slice(1), user: user.charAt(0).toUpperCase() + user.slice(1) }; }}
// Convert consolidated positions to array and sort by ticker var output = Object.values(consolidatedPositions); output.sort((a, b) => a.ticker.localeCompare(b.ticker));
// Add SN and prepare final sorted output var sortedOutput = output.map((item, index) => [ index + 1, // SN item.ticker, item.type, Number(item.quantity.toFixed(4)), // Format quantity item.avgPrice.toFixed(2), // Format average price item.assetClass, item.broker, item.user ]);
// Output to "Portfolio" sheet without affecting formatting if (sortedOutput.length > 0) { portfolioSheet .getRange(4, 1, sortedOutput.length, sortedOutput[0].length) .setValues(sortedOutput); portfolioSheet .getRange(4, 4, sortedOutput.length, 2) .setNumberFormat('#,##0.00'); } else { Logger.log("No open positions to display."); }
// Record the last update date and time let now = new Date(); let formattedNow = Utilities.formatDate(now, "Asia/Kolkata", "dd-MMM-yyyy HH:mm:ss"); portfolioSheet.getRange('V1').setValue(formattedNow);
Logger.log("Open portfolio updated successfully."); }"
what is direfence between both script.
result to dono se same are rahe h wo bhi correct
- Assistant
Both scripts you provided are designed to update a "Portfolio" sheet based on data from a "Trade Log" sheet in Google Sheets. While they achieve the same end result—correctly reflecting open positions—the two scripts implement different approaches and contain distinct elements. Below is a comprehensive comparison highlighting their differences:
1. Structure and Organization
-
First Script:
- Utilizes a single
generateTradefunction to create trade objects. - Manages positions using a
positionsMapwhere keys includeticker,broker,user,assetClass, andtradeType. - Handles various actions (
buy,sell,sellshort,buytocover,transferfrom) directly within the main loop.
- Utilizes a single
-
Second Script:
- Introduces two additional helper functions:
addPositionandremovePositions. - Uses a
positionsMapwith keys composed ofticker,assetClass,broker, anduser(excludingtradeType). - Incorporates a
pendingTransfersobject to manage transfer actions more explicitly. - Separates the handling of different actions (
buy,sell_short,transferfrom,transferto) into distinct operations.
- Introduces two additional helper functions:
2. Handling of Transfer Actions
-
First Script:
- Handles
transferfromand implicitlytransfertowithin the main loop without explicitly separating them. - Processes transfers by assuming the next row corresponds to the transfer counterpart.
- Handles
-
Second Script:
- Explicitly manages
transferfromandtransfertoactions using apendingTransfersobject. - Ensures that transfers are matched correctly by using unique
transferKeyidentifiers based onticker,assetClass,quantity,date,broker, anduser. - Enhances reliability by verifying the existence of corresponding transfer actions and logging warnings if mismatches occur.
- Explicitly manages
3. Position Management
-
First Script:
- Defines a
generateTradefunction to create trade objects with properties likeshares,price,fees, etc. - Uses FIFO (First-In-First-Out) logic directly within the action handlers to manage buying and selling.
- Defines a
-
Second Script:
- Implements
addPositionandremovePositionshelper functions to modularize position management. addPositionadds new trades to thepositionsMap.removePositionshandles the removal or adjustment of existing positions based on sell actions, ensuring better separation of concerns and potentially easier maintenance.
- Implements
4. Handling Corporate Actions (e.g., Stock Splits)
-
First Script:
- Processes stock splits by iterating over
positionsMapkeys that start with the relevantticker. - Adjusts
sharesandpricedirectly within each trade object.
- Processes stock splits by iterating over
-
Second Script:
- Similarly processes stock splits but includes more robust error handling.
- Validates the split ratio format and ensures that division by zero does not occur.
- Adjusts
quantityandpricewith added safeguards and more descriptive logging for invalid data.
5. Data Consolidation and Output
-
First Script:
- Consolidates positions by iterating over
positionsMapand calculating total shares, total cost, and average price. - Prepares the output array (
returnArry) and writes it directly to the "Portfolio" sheet.
- Consolidates positions by iterating over
-
Second Script:
- Consolidates positions into a
consolidatedPositionsobject with more detailed properties. - Capitalizes
assetClass,broker, anduserfor better readability. - Sorts the final output by
tickerand uses a separatesortedOutputarray for writing to the sheet. - Ensures that number formatting is applied correctly for quantities and average prices.
- Consolidates positions into a
6. Logging and Error Handling
-
First Script:
- Logs missing headers and invalid dates.
- Logs warnings when processing sell actions without available positions.
-
Second Script:
- Extends logging to cover more scenarios, such as invalid transfer actions and mismatched transfers.
- Provides more descriptive log messages to aid in troubleshooting.
7. Header Management
Both scripts handle headers similarly by checking if the existing headers match the expected ones and updating them if necessary. However, the second script adds a comment indicating that "fees" have been added to the expected headers, suggesting an enhancement or correction in the header management.
8. Code Formatting and Readability
-
First Script:
- Uses template literals (e.g.,
${ticker}|${broker}|${user}|${assetClass}|${tradeType}) within the loop, which might have been intended for string interpolation but is incorrectly formatted without backticks.
- Uses template literals (e.g.,
-
Second Script:
- Corrects this by using quotation marks around template literals where necessary or adjusts the syntax to avoid potential errors.
- Improves overall code readability by organizing functions and clearly separating different logical sections.
9. Improvements in Second Script
The second script introduces several enhancements over the first:
- Modular Functions:
addPositionandremovePositionsmake the code more modular and easier to maintain. - Enhanced Transfer Handling: The introduction of
pendingTransfersensures that transfers are accurately matched and processed. - Robust Error Handling: More comprehensive checks and logging provide better insights into potential issues.
- Data Capitalization: Improves the readability of the output by capitalizing certain fields.
- Sorting and Formatting: Ensures that the output is well-organized and correctly formatted in the "Portfolio" sheet.
Conclusion
While both scripts achieve the primary goal of updating the "Portfolio" sheet based on the "Trade Log," the second script is more robust, modular, and maintainable. It introduces better handling for complex actions like transfers and corporate actions, provides enhanced error checking and logging, and organizes the code in a clearer, more structured manner. If both scripts produce the same correct results, the second script's additional features and improved structure can offer better scalability and easier debugging in the long run.
-