Interactive Brokers Google Sheets

/**
 * IBRK Flex Query.
 *
 * From IBKR side you need enable flex service - this can be done in "Flex Web Service" in settings menu.
 * While enabling flex service - copy your token.
 * After enabling flex service go to "Flex Queries" under "Performance & Reports" menu and create one.
 * Each created report will have its own query id - copy it as well.
 * Keep in mind that this function will grab only first section of report, so if you want multiple sections just create multiple queries.
 * The report retrieval is done in two steps:
 * - step 1: request report generation, requires token and query id, will return report code and url
 * - step 2: retrieve generated report
 * Notes:
 * - when you prepared your report it is a good idea to enable cache so everything will work much faster
 * - if report is really huge try to increase to something like 5 seconds
 *
 * @param {number} token Token from Settings \ Flex Services menu.
 * @param {number} query Query ID of Flex Query from Reports \ Flex Query menu.
 * @param {bool} headers Add header names to result. Default TRUE.
 * @param {bool} cache Cache results for 6 hours which is maximum for Google Sheets. Default FALSE.
 * @param {number} sleep Report generation takes some time, so we are waiting given amount of milliseconds before proceeding. Default is 2000 which is 2 seconds.
 * @return Query results.
 * @customfunction
*/
function IBKRFLEXQUERY(token, query, headers=true, cache=false, sleep=2000) {
  // Step 0: optional, return cached results
  var cacheService = CacheService.getDocumentCache();
  var cacheKey = token + "." + query;
  if (cache === true && cacheService.get(cacheKey)) {
    return JSON.parse(cacheService.get(cacheKey));
  }

  // Step 1: request report generation
  var url = "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?v=3&t="+token+"&q="+query;
  var xml = UrlFetchApp.fetch(url).getContentText();
  var root = XmlService.parse(xml).getRootElement();
  var code = root.getChild("ReferenceCode").getValue();
  url = root.getChild("Url").getValue();
  Utilities.sleep(sleep);

  // Step 2: retrieve generated report
  url = url + "?v=3&t="+token+"&q=" + code
  xml = UrlFetchApp.fetch(url).getContentText();
  var nodes = XmlService.parse(xml).getRootElement().getChild("FlexStatements").getChild("FlexStatement").getChildren()[0].getChildren();

  var items = [];
  for(var node of nodes) {

    // optional: add headers
    if (items.length === 0 && headers) { items.push(node.getAttributes().map(attr => attr.getName())); }

    var item = [];
    for(var attr of node.getAttributes()) {
      var text = attr.getValue();
      if (text.match(/^-?[\d,\.]+$/)) {
        item.push(parseFloat(text));
      } else {
        item.push(text);
      }
    }
    items.push(item);
  }

  // Step 0: optional, store results in cache for subsequent requests
  if (cache === true) {
    cacheService.put(cacheKey, JSON.stringify(items), 21600);
  }

  return items;
}

TOKEN="1417**************"
QUERY_ID="866660"

curl -X POST \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "q=$QUERY_ID&v=3&t=$TOKEN" \
  "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest"

<FlexStatementResponse timestamp='14 October, 2023 03:57 AM EDT'>
  <Status>Success</Status>
  <ReferenceCode>5045169185</ReferenceCode>
  <Url>https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.GetStatement</Url>
</FlexStatementResponse>

curl -X POST \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "q=5045169185&v=3&t=$TOKEN" \
  "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.GetStatement"

<FlexQueryResponse queryName="portfolio" type="AF">
  <FlexStatements count="1">
    <FlexStatement fromDate="2023-10-13" toDate="2023-10-13" period="LastBusinessDay" whenGenerated="2023-10-14 03:57:57">
      <OpenPositions>
        <OpenPosition symbol="VZ" position="10" />
        <OpenPosition symbol="XRX" position="4" />
      </OpenPositions>
    </FlexStatement>
  </FlexStatements>
</FlexQueryResponse>