Tradernet.ua Cashflow Statements Parser

README.md

Tradernet.ua Cashflow Statements Parser

Usage

1. override a script and append bootstrapper.js to it:
2. compile tradernet-statements.ts
3. run dev-server.ts
4. run ffScrap() in the console
5. upload the downloaded CSV to Google Sheets
6. In ActionScript editor, create PARSE_TRADERNET_DATA.gs
7. add the parser expression to the header cell next to the rightmost column: ={"Type", "Ticker", "Qty"; PARSE_TRADERNET_DATA(J2:J, D2:D)}
8. Turn on AutoFilter
9. add the aggregate expression to the header cell next to the rightmost column: ={"Count:", SUBTOTAL(2, G2:G), "Total:", SUBTOTAL(9, G2:G); "", "", "", ""}

bootstrapper.js

(function() {
    const WS_URL = 'wss://localhost';
    const JS_URL = 'http://localhost:3000/';
    let socket;

    function connect() {
        const socket = new WebSocket(WS_URL);
        
        socket.onmessage = (event) => {
            const data = JSON.parse(event.data);
            console.log(`message: ${data.type}`)
            if (data.type === 'RELOAD') {
                console.log(`%c🔄 Script change detected: ${data.file}`, "color: #3498db; font-weight: bold;");
                injectScript(data.file);
            }
        };

        socket.onclose = () => setTimeout(connect, 2000); // Auto-reconnect
    }

    function injectScript(filePath) {
        // Remove existing instance if any
        const oldScript = document.getElementById('tradernet-aggregator-live');
        if (oldScript) oldScript.remove();

        const script = document.createElement('script');
        script.id = 'tradernet-aggregator-live';
        // Cache busting is key for local dev
        const rnd = 1; // Date.now();
        script.src = `${JS_URL}${filePath}?t=${rnd}`; 
        script.onload = () => {
            console.log("%c🚀 New script version loaded and ready.", "color: #2ecc71;");
            // Optionally auto-trigger: window.scraper.run("2020-01-01");
        };
        document.head.appendChild(script);
    }

    // connect();
    injectScript('src/tradernet-statements.js');
    // console.log("%c📡 LiveBridge Active. Waiting for local rebuilds...", "color: #9b59b6;");
})();

dev-server.ts

import * as path from "node:path";
import { createServer } from "node:https";
import { readFileSync } from "node:fs";

// @ts-ignore
import express from "express";
import { WebSocketServer } from "ws";
import chokidar from "chokidar";

const HTTP_PORT = 3000;
const WS_PORT = 443;
// @ts-ignore
const distPath = path.join(process.cwd(), "dist");

// 1. Serve the compiled JS
const app = express();

const staticHeaders: Parameters<typeof express.static>[1] = {
  setHeaders: (res) => {
    res.setHeader("Access-Control-Allow-Origin", "https://tradernet.ua");
  },
};
app.use(express.static("dist", staticHeaders));
app.use("/mock", express.static("mock", staticHeaders));
app.use("/src", express.static("src", staticHeaders));
app
  .listen(HTTP_PORT, () =>
    console.log(`[HTTP] Serving dist/ on http://localhost:${HTTP_PORT}`)
  )
  .on("request", function (req, res) {
    console.log("req", req.method, req.url);
  });

// 2. WebSocket for reload signals
const server = createServer({
  cert: readFileSync("./crt/wss.tradernet.dev.pem"),
  key: readFileSync("./crt/wss.tradernet.dev-key.pem"),
});
const wss = new WebSocketServer({ server });

wss.on("connection", function (e, req) {
  console.log("Client connected");
  e.send(JSON.stringify({ type: "CONNECTED" }));
  e.send(
    JSON.stringify({ type: "RELOAD", file: "src/tradernet-statements.js" })
  );

  e.once("close", () => {
    console.log("Client disconnected");
  });
});

server.listen(WS_PORT, () => {
  console.log(
    `🚀 Secure Signal Server running on wss://wss.tradernet.dev:${WS_PORT}`
  );
});

chokidar
  .watch(distPath, {
    ignored: (path, stats) => !!stats?.isFile() && !path.endsWith(".js"),
  })
  .on("change", (filePath, stats) => {
    const relPath = path.relative(distPath, filePath);
    console.log(`[Rebuild] ${relPath} changed. Sending signal...`);
    wss.clients.forEach((client) =>
      client.send(JSON.stringify({ type: "RELOAD", file: relPath }))
    );
  });

PARSE_TRADERNET_DATA.gs

/**
 * Parses Tradernet comments based on type codes.
 * * @param {string[][]} comments The range of comments (J2:J).
 * @param {string[][]} types The range of type codes (D2:D).
 * @return {string[][]} Processed array of strings.
 * @customfunction
 */
function PARSE_TRADERNET_DATA(comments, types) {
  // 1. Guard against single-cell inputs or empty ranges
  if (!Array.isArray(comments)) return [["Error: Input must be a range"]];

  return comments.map((row, index) => {
    const comment = row[0] || "";
    const typeCode = types[index] ? types[index][0] : "";
    
    // Returns [Action, Ticker/Context, Qty/Value]
    return processEntry(comment, typeCode);
  });

  // 2. Map through the first array and use index to pick the adjacent type
  return comments.map((row, index) => {
    const comment = row[0];         // Current comment in row
    const typeCode = types[index] ? types[index][0] : ""; // Current type in row

    // Skip empty rows
    if (!comment) return [""];

    // 3. Delegation logic
    try {
      return processEntry(comment, typeCode);
    } catch (e) {
      return [`Error: ${e.message}`];
    }
  });
}

/**
 * Internal logic for actual parsing
 */
function processEntry(comment, type) {
  let match;
  let tickerMatch;

  switch (type) {
    case 'commission_for_trades':
      match = comment.match(/\(Trade (\d+) (\w+) ([\w.]+)/);
      return match ? [match[2].toUpperCase(), match[3], ""] : ["TRADE_ERR", "", ""];

    case 'commission_for_short':
      match = comment.match(/security ([\w.]+)\. Number of securities on .*?: (-?[\d.]+), value of securities: ([\d.]+)/);
      return match ? ["SHORT", match[1], match[2]] : ["SHORT_ERR", "", ""];

    case 'dividend':
      tickerMatch = comment.match(/\((?:.*\()?\s*([\w.]+)\s*\)\)/) || comment.match(/\(([\w.]+)\)/);
      const balMatch = comment.match(/Balance on the.*?date is\s*(\d+)/i);
      const label = type === 'dividend' ? "DIV" : "TAX";
      return (tickerMatch && balMatch) ? [label, tickerMatch[1], balMatch[1]] : [`${label}_ERR`, "", ""];

    case 'tax':
      // 1. Ticker: Додаємо підтримку пробілів всередині дужок ( LLY.US )
      let tTicker = comment.match(/\((?:.*\()?\s*([\w.]+)\s*\)\)/) || 
                    comment.match(/\(\s*([\w.]+)\s*\)/) ||
                    comment.match(/(?:security|share|paper)\s+([\w.]+)/i);
      
      // 2. Balance: Шукаємо цифри, які йдуть ПІСЛЯ слова "Balance" та "is/date"
      // Використовуємо \d+$ для прив'язки до самого кінця рядка
      let tBalance = comment.match(/Balance.*?is\s*(\d+)\s*$/i) ||
                     comment.match(/Balance.*?date\s+(\d+)\s*$/i);
      
      const tickerVal = tTicker ? tTicker[1].trim() : null;
      const balanceVal = tBalance ? tBalance[1] : null;

      return (tickerVal && balanceVal) ? ["TAX", tickerVal, balanceVal] : ["TAX_ERR", "", ""];

    case 'commission_for_margin':
      // Look for the number at the very end of the string ($)
      // This ignores the :59 seconds in the timestamp
      match = comment.match(/balance\s+(?:as\s+at|on)\s+.*:\s*([\d.]+)\s*$/i);
      
      // Fallback: if there's trailing text, match the number after the final colon
      if (!match) {
        match = comment.match(/:\s*([\d.]+)(?!.*:)/);
      }
      
      return match ? ["DEBT", "MARGIN", match[1]] : ["DEBT_ERR", "", ""];

    case 'maturity':
      match = comment.match(/\(([\w.]+)\)/);
      const priceMatch = comment.match(/Price per security:.*?\s([\d.]+)/i);
      return (match && priceMatch) ? ["MATURITY", match[1], priceMatch[1]] : ["MAT_ERR", "", ""];

    case 'bank':
      if (comment.includes('Transfer to trading account')) return ["CROSS", "BANK", ""];
      return ["BANK", "OTHER", ""];

    case "commission_refund":
      return "REFUND";
    
    default:
      return [type.startsWith("commission_") ? "FEE" : "", "", ""];
  }
}

function extractTicker(str) {
  const match = str.match(/\((?:.*\()?\s*([\w.]+)\s*\)\)/) || 
                str.match(/\(([\w.]+)\)/) ||
                str.match(/(?:security|paper|shares)\s+([\w.]+)/i);
  return match ? match[1] : null;
}

tradernet-statements.ts

const MOCK_DATA_URL = "http://localhost:3000/mock/statements-2026-01-07.json";
const START_DATE = new Date("2020-01-01");

/**
 * Tradernet Statement Aggregator
 * Features: Recursive date-splitting, Deduplication, UTF-8 CSV Export
 */

interface CashFlowResponseItem {
  id: number;
  date: string;
  datetime: string;
  type_code: (typeof TYPE_CODES)[number];
  type_code_name: string;
  account: string;
  sum: string;
  sumRaw: string;
  currency: string;
  comment: string;
  min_step: string;
  icon: string;
}

const TYPE_CODES = [
  "agent_fee",
  "bank",
  "block",
  "card",
  "commission_for_margin",
  "commission_for_other",
  "commission_for_short",
  "commission_for_subscriptions",
  "commission_for_trades",
  "commission_refund",
  "conversion",
  "corporate_action_compensation",
  "corporate_action_compensation",
  "dividend",
  "intercompany",
  "maturity",
  "otc_trade",
  "scores_rebate",
  "split",
  "tax",
  "unblock",
] as const;

/** Describes the column order in the XLS file */
const CASHFLOW_ROW_HEADERS: ReadonlyArray<keyof CashFlowResponseItem> = [
  "id",
  "date",
  "datetime",
  "type_code",
  "type_code_name",
  "account",
  "sum",
  "sumRaw",
  "currency",
  "comment",
  "min_step",
  "icon",
] as const;

class TradernetScraper {
  private allData: Map<number, CashFlowResponseItem> = new Map();
  private userId: string;
  private mockData: CashFlowResponseItem[] | null = null; // Add mock data field

  constructor() {
    this.userId = this.getUserId();
    this.log(`initialized, userId: ${this.userId}`);
  }

  private log(...args: any[]) {
    console.log(`[${TradernetScraper.name}]`, ...args);
  }

  private getUserId(): string {
    const match = document.cookie.match(/user_id=(\d+)/);
    if (!match)
      throw new Error("User ID not found in cookies. Are you logged in?");
    return match[1];
  }

  private async fetchChunk(
    dateFrom: string,
    dateTo: string
  ): Promise<{ items: CashFlowResponseItem[]; total: number }> {
    const formData = new FormData();
    const query = {
      cmd: "getUserCashFlows",
      params: {
        user_id: parseInt(this.userId),
        filter: {
          logic: "and",
          filters: [
            { field: "date", operator: "eqormore", value: dateFrom },
            { field: "date", operator: "eqorless", value: dateTo },
          ],
        },
      },
    };

    formData.append("q", JSON.stringify(query));

    const response = await fetch(
      "https://tradernet.ua/api?cmd=getUserCashFlows",
      {
        method: "POST",
        body: formData,
      }
    );

    if (!response.ok) throw new Error(`API Error: ${response.statusText}`);

    const data = await response.json();
    return {
      items: data.cashflow ?? [],
      total: data.total ?? 0,
    };
  }

  /**
   * Processes a date range. If the 1000-row limit is hit, it splits the range.
   */
  private async processRange(start: Date, end: Date) {
    const dateFrom = start.toISOString().split("T")[0];
    const dateTo = end.toISOString().split("T")[0];

    console.log(`%c🔍 Checking: ${dateFrom} to ${dateTo}`, "color: #3498db");

    const { items, total } = await this.fetchChunk(dateFrom, dateTo);

    // If we hit the 1000 limit and there's more data, we must subdivide
    if (items.length >= 1000 && total > items.length) {
      console.warn(
        `%c⚠️ Limit reached (${total} items). Splitting range...`,
        "color: #f1c40f"
      );

      const mid = new Date(
        start.getTime() + (end.getTime() - start.getTime()) / 2
      );
      await this.processRange(start, mid);

      const dayAfterMid = new Date(mid);
      dayAfterMid.setDate(dayAfterMid.getDate() + 1);
      await this.processRange(dayAfterMid, end);
    } else {
      items.forEach((item) => this.allData.set(item.id, item));
      console.log(
        `%c✅ Added ${items.length} items. Total unique: ${this.allData.size}`,
        "color: #2ecc71"
      );
    }

    // Throttling to be polite to the API
    await new Promise((res) => setTimeout(res, 400));
  }

  private exportToCsv(data: readonly CashFlowResponseItem[]) {
    const headers = Object.keys(data[0]);

    if (new Set(headers).difference(new Set(CASHFLOW_ROW_HEADERS)).size !== 0) {
      console.error(
        `%c❌ Data schema has changed. Sample:\n${JSON.stringify(
          data[0],
          null,
          2
        )}`,
        "font-weight: bold; font-size: 14px; color: #88192cff"
      );
    }

    const csvContent = [
      CASHFLOW_ROW_HEADERS.join(","),
      ...data.map((row) =>
        CASHFLOW_ROW_HEADERS.map((h) => {
          const value = row[h];

          if (typeof value === "string") {
            const num = Number(value);
            if (!isNaN(num)) return num;
          }

          return typeof value === "number"
            ? value
            : `"${String(value || "").replace(/"/g, '""')}"`;
        }).join(",")
      ),
    ].join("\n");

    const blob = new Blob(["\uFEFF" + csvContent], {
      type: "text/csv;charset=utf-8;",
    });
    const url = URL.createObjectURL(blob);
    const link = document.createElement("a");
    link.href = url;
    link.download = `tradernet_full_export_${
      new Date().toISOString().split("T")[0]
    }.csv`;
    link.click();
    console.log(
      "%c💾 Export Complete!",
      "font-weight: bold; font-size: 14px; color: #9b59b6"
    );
  }

  public async run(isMockData = false) {
    this.allData.clear();
    console.time("Execution Time");

    try {
      let data: CashFlowResponseItem[];

      if (isMockData) {
        if (!this.mockData) {
          // Fetch mock data only once
          const response = await fetch(MOCK_DATA_URL);
          this.mockData = (await response.json()) as CashFlowResponseItem[];
          console.log(
            "%c 🧪 Using MOCK data",
            "color: #ff6b6b; font-weight: bold"
          );
        }
        data = this.mockData;
      } else {
        await this.processRange(START_DATE, new Date());

        data = Array.from(this.allData.values()).sort((a, b) => b.id - a.id); // Newest first
      }

      if (data.length === 0) return console.log("No data to export.");

      this.exportToCsv(data);
    } catch (error) {
      console.error("Scraper failed:", error);
    }

    console.timeEnd("Execution Time");
  }
}

(window as any).ffScrap = (() => {
  // Take parameter from the outside
  const scraper = new TradernetScraper();

  console.log(
    "%c📡 TradernetScraper initialized. Use in console:\nffScrap(true)\nwhere true instructs to use the mock data",
    "color: #b27af1ff;"
  );

  return (isMockData = false) => {
    scraper.run(isMockData); // Pass parameter to the instance
  };
})();