Custom import template - dealing with JSON complex objects

Leszek · May 19, 2025, 4:36pm

I’m looking to import transactions from Vanguard UK SIPP account. There’s no export, but their page loads the transactions for display with a neat JSON response. However, I’m a bit lost how to parse anything more complex than simple rows into useful import values.

Sample:

{
  "Result": {
    "Pager": {
      "CurrentPage": 1,
      "MaximumRow": 20,
      "MinimumRow": 1,
      "ResultsPerPage": 20,
      "TotalPages": 1,
      "TotalRecords": 19
    },
    "Results": [
      {
        "Id": 37464373,
        "InvestmentName": "FTSE Global All Cap Index Fund Accumulation",
        "SubAccountName": "Pension",
        "Type": 1,
        "Status": 4,
        "BuyOrSell": 2,
        "Amounts": {
          "Type": 1,
          "Value": { "Amount": 21.72, "Currency": 40 },
          "Quantity": 0.0954,
          "EstimatedTradingAmount": { "Amount": 21.72, "Currency": 40 },
          "TotalAmount": { "Amount": 21.72, "Currency": 40 },
          "UnitPrice": { "Amount": 227.76, "Currency": 40 }
        },
        "DateCreated": "2025-03-03T06:11:21.143",
        "DateAuthorised": "2025-03-03T06:11:21.567",
        "DateCompleted": "2025-03-04T09:15:30.893",
        "DateCancelled": null,
        "ExecutionType": 6,
        "Instrument": {
          "ProductId": 87432,
          "Name": "FTSE Global All Cap Index Fund Accumulation",
          "Sedol": "BD3RZ58",
          "Isin": "GB00BD3RZ582",
          "Ric": null,
          "Cusip": "",
          "Currency": 40,
          "ProductCode": "NGLY.GB",
          "CitiCode": "NGLY",
          "Sector": {
            "Id": 333,
            "Code": "O:UNCLAS",
            "Name": "Unclassified",
            "SectorProvider": 1
          },
          "AssetClass": "VANDEFCOM",
          "Type": 3,
          "InstrumentGroups": [
            { "Id": 2, "Name": "Equity" },
            { "Id": 6, "Name": "Global" }
          ],
          "RiskLevelId": 0,
          "SustainabilityDisclosure": {
            "ProductlId": 87432,
            "SdrLabelId": 0,
            "HasUKSdrLabel": false,
            "IsOverseasRecognisedScheme": false,
            "IsUKRecognisedScheme": false,
            "ConsumerFacingDisclosureUrl": ""
          }
        },
        "IsSwitch": false,
        "SubAccountHierarchyId": "000-0000228837",
        "RegularPaymentExpectedCompletionDate": null
      }
    ]
  }
}

So, Results is an array of operations, simple enough. And I could use InvestmentName for looking up the instrument (this pops up as an option in the preset editor) - but Instrument.Isin would be more precise and I can’t work out how to get at it. Same for Amounts.UnitPrice.Amounts and Amounts.Value.Amount.

rafal · May 19, 2025, 5:02pm

This is absurd how some brokers make it hard to export your own data

Still, you can use formulas to get this. You need to switch to the Advanced Mode, and once there, you add a formula transform by clicking on fx. In the formula, x is the value, which in case of Instrument or Amounts column will be an object. You can access deeper values with a dot notation (x.Value.Amount).

Note, that the formula is first, then the value extracted by it is made absolute.

You can do the same for any other field.

And remember you can add mappings for all these cryptic numbers they use - again, in any field (eg type and currency).

You can also do both - first extract the value from an object, and then map it to something.