IBKR Flex Query into Google Sheets, Excel or Python

import os
import time
import xml.etree.ElementTree as ET

import pandas as pd
import requests

BASE = "https://ndcdyn.interactivebrokers.com/AccountManagement/FlexWebService"
TOKEN = os.environ["IBKR_FLEX_TOKEN"]
QUERY_ID = os.environ["IBKR_FLEX_QUERY_ID"]


def call(endpoint, q):
    reply = requests.get(
        f"{BASE}/{endpoint}",
        params={"t": TOKEN, "q": q, "v": "3"},
        headers={"User-Agent": "Python/3"},
        timeout=60,
    )
    reply.raise_for_status()
    return ET.fromstring(reply.content)


def fail(reply):
    raise SystemExit(f"{reply.findtext('ErrorCode')}: {reply.findtext('ErrorMessage')}")


sent = call("SendRequest", QUERY_ID)
if sent.findtext("Status") != "Success":
    fail(sent)

for _ in range(20):
    time.sleep(10)  # 1019 means the statement is still being built
    statement = call("GetStatement", sent.findtext("ReferenceCode"))
    if statement.tag == "FlexQueryResponse":
        break
    if statement.findtext("ErrorCode") != "1019":
        fail(statement)
else:
    raise SystemExit("The statement was not ready after 200 seconds")

positions = pd.DataFrame([row.attrib for row in statement.iter("OpenPosition")])
positions.to_csv("ibkr_positions.csv", index=False)
print(positions)

const BASE = "https://ndcdyn.interactivebrokers.com/AccountManagement/FlexWebService";

function flexCall(endpoint, q) {
  const token = PropertiesService.getScriptProperties().getProperty("IBKR_FLEX_TOKEN");
  const url = `${BASE}/${endpoint}?t=${encodeURIComponent(token)}&q=${encodeURIComponent(q)}&v=3`;
  return XmlService.parse(UrlFetchApp.fetch(url).getContentText()).getRootElement();
}

function fail(reply) {
  throw new Error(`${reply.getChildText("ErrorCode")}: ${reply.getChildText("ErrorMessage")}`);
}

function importIbkrPositions() {
  const queryId = PropertiesService.getScriptProperties().getProperty("IBKR_FLEX_QUERY_ID");
  const sent = flexCall("SendRequest", queryId);
  if (sent.getChildText("Status") !== "Success") fail(sent);

  let statement;
  for (let attempt = 0; attempt < 20; attempt++) {
    Utilities.sleep(10000); // 1019 means the statement is still being built
    statement = flexCall("GetStatement", sent.getChildText("ReferenceCode"));
    if (statement.getName() === "FlexQueryResponse") break;
    if (statement.getChildText("ErrorCode") !== "1019") fail(statement);
  }
  if (statement.getName() !== "FlexQueryResponse") throw new Error("Not ready after 200 seconds");

  const rows = [];
  for (const account of statement.getChild("FlexStatements").getChildren("FlexStatement")) {
    const open = account.getChild("OpenPositions");
    if (open) rows.push(...open.getChildren("OpenPosition"));
  }
  if (rows.length === 0) return;
  const header = rows[0].getAttributes().map((attribute) => attribute.getName());
  const values = rows.map((row) =>
    header.map((name) => {
      const value = row.getAttribute(name)?.getValue() ?? "";
      return value !== "" && !isNaN(value) ? Number(value) : value;
    }),
  );

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName("IBKR") ?? book.insertSheet("IBKR");
  sheet.clearContents();
  sheet.getRange(1, 1, values.length + 1, header.length).setValues([header, ...values]);
}