Script to add stock splits and bonuses from Zerodha

corporate-actions.ts

import { GoogleSpreadsheet } from "google-spreadsheet";
import { JWT } from "google-auth-library";
import { writeFile, readFile } from "node:fs/promises";
import { parseFile } from "@fast-csv/parse";

const ActionType = {
  Split: "Split",
  Bonus: "Bonus",
} as const;

interface CorporateAction {
  symbol: string;
  date: Date;
  type: keyof typeof ActionType;
  ratio: [number, number];
}

interface Trade {
  symbol: string;
  date: Date;
  type: "buy" | "sell" | "bonus" | "split";
  quantity: number;
  price: number;
}

const roundToTwo = (num: number) =>
  Math.round((num + Number.EPSILON) * 100) / 100;

const serviceAccountAuth = new JWT({
  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  key: process.env.GOOGLE_PRIVATE_KEY,
  scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});

const doc = new GoogleSpreadsheet(
  "1yTedLYGJ8z-X2L7pNPyRnzJpelzzuKH_26vZSBfFjIY",
  serviceAccountAuth
);

console.log("Loading Google Sheet");
await doc.loadInfo();

const corporateActionSheets = doc.sheetsByIndex
  .filter((sheet) => sheet.title.startsWith("FY"))
  .sort((sheetA, sheetB) => {
    const yearA = parseInt(sheetA.title.split(" ")[1]);
    const yearB = parseInt(sheetB.title.split(" ")[1]);
    return yearA - yearB;
  });
console.log(
  "Sheets found:",
  corporateActionSheets.map((sheet) => sheet.title)
);

let corporateActions: CorporateAction[] = [];

for (const sheet of corporateActionSheets) {
  console.log("Loading sheet:", sheet.title);
  const rows = await sheet.getRows();
  console.log("Rows found:", rows.length);
  for (const row of rows) {
    const symbol = row.get("Trade symbol") as string;
    const segment = row.get("Segment") as string;
    const action = row.get("CA type") as keyof typeof ActionType;
    const status = row.get("Status") as string;
    const date = row.get("Ex-date/Listing date/delisting date") as string;
    const ratio = row.get("Ratio (A:B)") as string;

    if (
      segment === "EQ" &&
      status !== "Upcoming" &&
      Object.values(ActionType).includes(action)
    ) {
      corporateActions.push({
        symbol,
        date: new Date(date),
        type: action,
        ratio: ratio.split(":").map(Number) as [number, number],
      });
    }
  }
}

console.log("Corporate Actions found:", corporateActions.length);

console.log("Saving to file");
await writeFile(
  "output/corporate-actions.json",
  JSON.stringify(corporateActions, null, 2)
);

corporateActions = (
  JSON.parse(
    await readFile("output/corporate-actions.json", "utf-8")
  ) as CorporateAction[]
)
  .map((action) => ({
    ...action,
    date: new Date(action.date),
  }))
  .sort((a, b) => a.date.getTime() - b.date.getTime());

const trades: Trade[] = [];

await new Promise((resolve) => {
  parseFile("input/tradebook.csv", { headers: true })
    .on("data", (row) => {
      trades.push({
        symbol: row.symbol,
        date: new Date(row.trade_date),
        type: row.trade_type,
        quantity: Number(row.quantity),
        price: Number(row.price),
      });
    })
    .on("end", resolve);
});

const tradedSymbols = new Set(trades.map((trade) => trade.symbol));

const ledgerEntries: string[] = [];

for (const action of corporateActions) {
  if (!tradedSymbols.has(action.symbol)) {
    continue;
  }

  const tradesForSymbol = trades.filter(
    (trade) => trade.symbol === action.symbol && trade.date < action.date
  );

  if (tradesForSymbol.length === 0) {
    continue;
  }

  const lastTrade = tradesForSymbol[tradesForSymbol.length - 1];
  const oldQuantity = tradesForSymbol.reduce(
    (acc, trade) =>
      acc + (trade.type === "sell" ? -trade.quantity : trade.quantity),
    0
  );

  if (oldQuantity === 0) {
    continue;
  }

  let newQuantity = oldQuantity;
  let newPrice = lastTrade.price;

  const formattedDate = action.date.toISOString().split("T")[0];
  const ratio = action.ratio[0] / action.ratio[1];

  if (action.type === ActionType.Split) {
    newQuantity = Math.floor(oldQuantity * ratio);
    newPrice = roundToTwo(lastTrade.price / ratio);
    ledgerEntries.push(`${formattedDate} ${
      action.symbol
    } Stock split: ${action.ratio.join(":")}
    Assets:Stocks:${action.symbol}    -${oldQuantity} ${action.symbol} @ ${
      lastTrade.price
    } INR
    Assets:Stocks:${action.symbol}    ${newQuantity} ${
      action.symbol
    } @ ${newPrice} INR
`);
  } else if (action.type === ActionType.Bonus) {
    newQuantity = oldQuantity + Math.floor(oldQuantity * ratio);
    newPrice = roundToTwo(
      lastTrade.price / (action.ratio[0] + action.ratio[1])
    );
    ledgerEntries.push(`${formattedDate} ${
      action.symbol
    } Bonus: ${action.ratio.join(":")}
    Assets:Stocks:${action.symbol}    ${newQuantity - oldQuantity} ${
      action.symbol
    } @ ${newPrice} INR
    Equity:Stocks:${action.symbol}
`);
  } else {
    throw new Error("Unknown action type");
  }

  trades.push({
    symbol: action.symbol,
    date: action.date,
    type: action.type === ActionType.Split ? "split" : "bonus",
    quantity: newQuantity - oldQuantity,
    price: newPrice,
  });
}

console.log(ledgerEntries.join("\n"));

package.json

{
  "name": "corporate-actions",
  "module": "corporate-actions.ts",
  "type": "module",
  "devDependencies": {
    "@types/bun": "latest"
  },
  "peerDependencies": {
    "typescript": "^5.0.0"
  },
  "dependencies": {
    "@fast-csv/parse": "^5.0.0",
    "google-auth-library": "^9.4.2",
    "google-spreadsheet": "^4.1.1"
  }
}