The Transactions Query API allows clients to query for transactions based on dates and attributes, providing a way for partners and clients to get transaction data back into their data warehouses.

Base Route/v1/transactions/query
EndpointsPOST
/v1/transactions/query
ProducesJSON
PaginationNo
Application Permissions Required"API Access: Create, edit, and delete"

"Transaction firm view: View only" and "Transactions: View-only" to extract view data.
OAuth ScopesTRANSACTIONS OR TRANSACTIONS_WRITE

Resource Overview

The resource object will return attributes specified by columns in successful POST requests for the Transactions Query API. A meta-object will also be returned in the response detailing the columns used in the query.

Parameters

Required Parameters:

ParameterDescriptionExample
columnList of column attribute keys. [String].["trade_date", "security"]
start_dateThe start date of the time period of transaction data. String.

"YYYY-MM-DD"
"2021-09-05"
end_dateThe end date of the time period of transaction data. String.

"YYYY-MM-DD"
"2021-09-06"
portfolio_typeThe type of portfolio. String.

Supported Values:

- FIRM
- FIRM_ACCOUNTS
- FIRM_CLIENTS
- FIRM_HOUSEHOLDS
- FIRM_UNVERIFIED_ACCOUNTS
- GROUP
- ENTITY
"ENTITY"
portfolio_id or external_ids See below.The ID of a portfolio configured in Addepar. Number or [Number].

A portfolio can be either an entity (i.e. a client, account, legal entity etc.) or a group of entities.

If the portfolio_type is FIRM, the portfolio_id must be 1.
7

[22, 24]
external_idsExternal id of the entity/group being queried for. Array of Objects.[{ "external_type_key": "salesforce", "external_id": "salesforce_external_id" }]
Transaction Columns

ColumnAppears in Addepar As
account_numberAccount #
accrued_incomeSnapshot Accrued Income
are_tax_lots_ignoredAre Custodian Tax Lots Ignored
asset_classAsset Class
cash_accountPaid to(from)
clientClient
commentsComments
contextContext
currencyCurrency
cusipCUSIP
data_sourceData Source
data_typeSummary Data Level
descriptionDescription
direct_owner_idDirect Owner Entity ID
edited_online_dataEdited Online Data
associated_hidden_positionsAssociated Hidden Position(s)
entity_idEntity ID
ex_dateEx-Date
feesFees
fundFund
affected_by_hidden_positionsAffected by Hidden Position(s)
has_tax_lotsHas Tax Lots
has_wash_saleHas Wash Sale
is_verifiedIs Verified
direct_ownerDirect Owner
idID
isinISIN
limited_partnerLimited Partner
other_affected_assetsOther Affected Assets
ownership_typeOwnership Type
account_nameAccount Name
positionPosition
position_idPosition ID
posted_datePosted Date
price_factorPrice Factor
price_per_sharePrice Per Share
recallable_amountRecallable Amount
securitySecurity
model_typeSecurity Model Type
sedolSEDOL
grouping_configurationGrouping Configuration
tagTAG
ticker_symbolTicker Symbol
trade_dateTrade Date
settlement_dateSettlement Date
snapshot_unitsSnapshot Units
summarySummary
transaction_unitsTransaction Units
typeType
unitsUnits
units_changedUnits Changed
valueValue
vendor_idVendor ID
affects_adjusted_valueAffects Adjusted Value
affects_cost_basisAffects Cost Basis
affects_paid_in_capitalAffects Paid-in Capital
affects_unfunded_commitmentsAffects Unfunded Commitments
general_feeGeneral Fee
external_brokerage_feeExternal Broker Fee
internal_brokerage_feeInternal Broker Fee
other_government_taxOther Government Tax
counterparty_feeCounterparty Fee
entry_exit_feeEntry and Exit Fee
foreign_feeForeign Fee
matching_feeMatching/Confirmation Fee
market_feeMarket Fee
market_taxMarket Tax
other_feeOther Fee
stamp_taxStamp Duty
stock_exchange_taxStock Exchange Tax
stock_exchange_feeStock Exchange Fee
turnover_feeTurnover Fee
value_added_taxValue Added Tax (VAT)
withholding_taxWithholding Tax
last_edit_byLast Edited By
last_edit_dateLast Edited Date
modified_dateModified Date
general_distributionGeneral Distribution
return_of_capitalReturn of Capital
long_term_capital_gainLong-Term Capital Gain
short_term_capital_gainShort-Term Capital Gain
unknown_capital_gainUnknown Capital Gain
interest_incomeInterest Income
ordinary_incomeOrdinary Income
dividend_incomeDividend Income
total_distributionTotal Distribution
created_dateCreated Date

Query Transactions Data

Returns transaction data based on the given query parameters.

POST /v1/transactions/query

Example:

POST https://examplefirm.addepar.com/api/v1/transactions/query

{
  "data": {
    "type": "transaction_query",
    "attributes": {
      "columns": ["direct_owner", "security", "value", "trade_date"],
      "portfolio_type": "GROUP",
      "portfolio_id": 7,
      "start_date": "2021-02-01",
      "end_date": "2021-03-01",
      "include_online_valuations": false,
      "include_unverified": false,
      "include_deleted": true
    }
  }
}
HTTP/1.1 200

{
    "meta": {
        "columns": [
            "direct_owner",
            "security",
            "value",
            "trade_date"
        ]
    },
    "data": [
        {
            "id": "7",
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Person 1",
                    "entity_id": 22
                },
                "security": {
                    "name": "Holding Company 1 Display Name",
                    "entity_id": 21
                },
                "trade_date": "2000-01-01",
                "value": null
            }
        }
    ],
    "included": [],
    "links": {
        "next": null
    }
}

Response Codes:

  • 200 OK: Success
  • 400 Bad Request: Improperly formatted query or lacking necessary data permissions
  • 403 Forbidden: User lacks API permission or has not granted the appropriate scope

Optional Parameters:

ParameterDescriptionExample
filtersA transactions query filter may be attached to a transactions query to filter rows from the result. Array of Objects.

To learn more, reference the Filter Object section below.
[{ "operator": "include", "type": "discrete", "values": ["11112222"], "attribute": "security" }]
sortingsA transactions query sorting may be attached to a transactions query to sort rows. Accepts up to 3 columns to sort by. Array of objects.

Default to trade date.
[{ "attribute": "trade_date", "ascending": false }]
limitThe max number of transactions returned by the query. Number.

Default to null (No limit, returns everything unless request times out.)
500
include_online_valuationsWhether online snapshots should be returned by the query. Boolean.

Default to false.
true
include_unverifiedWhether unverified transactions should be returned by the query. Boolean.

Default to false.
true
include_deletedWhether deleted online transactions should be returned by the query. Boolean.

Default to false.
true

Filter Object

Filters are used in Transaction Views to filter data. For example, to see only certain transactions, you can filter by start date.

Parameters:

ParametersDescriptionExample
attributeThe instance of the portfolio query attribute to be filtered on. String."start_date"
operatorSpecifies the operation of the filter. String.

Supported Values:

- include
- exclude
"include"
typeSpecifies the type of filter to be applied, which is based on the output type of the attribute. String.

Supported Values:
-DISCRETE

- NUMBER
- DATE
-STRING
"discrete"
values (plural)Required for type DISCRETE: values, a list of String values to match against, case-sensitive.["Transaction ID", "Entity ID"]
value (singular)Query transactions by comments or descriptions.

Required for type STRING: A single string with space separated tokens to match against is required.
"stock Apple"

This will match any comment or description containing the word stock or Apple.
dateRequired for type DATE:
period, instance of date_filter_period. Array of objects.

For more information on date filter, see below.
{"type": "static_time_period", "start": "2021-01-01", "end": "2021-12-30"}
numberRequired for type NUMBER:
start, numeric value denoting the starting point.
end, numeric value denoting the ending point.
1, 30.5

Example:

POST https://examplefirm.addepar.com/api/v1/transactions/query

{
  "data": {
    "type": "transaction_query",
    "attributes": {
      "columns": ["direct_owner", "value","trade_date"],
      "filters": [
        {
            "operator": "include",
            "type": "discrete",
            "values": ["Buy", 
                       "Sell"],
            "attribute": "direct_owner"
          }
      ],  
      "portfolio_type": "firm",
      "portfolio_id": [1],
      "start_date": "2021-09-05",
      "end_date": "2021-10-05",
      "include_online_valuations": false,
      "include_unverified": false,
      "include_deleted": true
    }
  }
}
HTTP/1.1 200

{
    "meta": {
        "columns": [
            "direct_owner",
            "value",
            "trade_date"
        ]
    },
    "data": [
        {
            "id": "1919",
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Integration Tester",
                    "entity_id": 183
                },
                "trade_date": "2005-01-01",
                "value": null
            }
        },
        {
            "id": "1960",
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Integration Tester",
                    "entity_id": 183
                },
                "trade_date": "2007-01-01",
                "value": -100000
            }
        },
        {
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Integration Tester",
                    "entity_id": 183
                },
                "trade_date": "2007-01-01",
                "value": 100000
            }
        },
        {
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Integration Tester",
                    "entity_id": 183
                },
                "trade_date": "2015-02-01",
                "value": 100100
            }
        }
    ],
    "included": [],
    "links": {
        "next": null
    }
}

Date Filters

To filter by dates, create a period object, then identify the type of date filter and use any required formatting.

ParametersDetailsExample
typeSpecifies the type of date filter to be applied. String.

Supported values:
-current_time_period
-inception_to_start_date_time_period
-relative_time_period
-since_inception_time_period
-static_time_period
-trailing_time_period
-custom_time_period
"current_time_period"
startRequired for static_time_period. String.

"YYYY-MM-DD"
"2021-01-01"
endRequired for static_time_period. String.

"YYYY-MM-DD"
"2021-12-30"
periodRequired for relative_time_periodand trailing_time_period. String.

P1D, P2K, P1Q, etc.
D = Days
K = weekdays
W = Weeks
M = Months
Q = Quarters
Y = Years
"P3K"

Static Time Period Example

POST https://examplefirm.addepar.com/api/v1/transactions/query

{
  "data": {
    "type": "transaction_query",
    "attributes": {
      "columns": ["direct_owner", "security", "value", "trade_date"],
      "filters": [
          { 
            "attribute": "trade_date",
            "operator": "include",
            "type": "date",
            "period": {
                "type": "static_time_period",
                "start": "2020-01-02",
                "end": "2020-01-03"
            }
        }
        ],
      "portfolio_type": "entity",
      "portfolio_id": [206],
   		"start_date": "2019-12-01",
      "end_date": "2019-12-31",
      "include_online_valuations": false,
      "include_unverified": false,
      "include_deleted": true
    }
  }
}
HTTP/1.1 200

{
    "meta": {
        "columns": [
            "direct_owner",
            "security",
            "value",
            "trade_date"
        ]
    },
    "data": [
        {
            "id": "1976",
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "11112222",
                    "entity_id": 208
                },
                "security": {
                    "name": "USD",
                    "entity_id": 20
                },
                "trade_date": "2020-01-03",
                "value": -100100
            }
        }
    ],
    "included": [],
    "links": {
        "next": null
    }
}

Trailing Time Period Example

POST https://examplefirm.addepar.com/api/v1/transactions/query


{
  "data": {
    "type": "transaction_query",
    "attributes": {
      "columns": ["direct_owner", "security", "value", "trade_date"],
      "filters": [
          { 
            "attribute": "trade_date",
            "operator": "include",
            "type": "date",
            "period": {
                "type": "trailing_time_period",
                "period": "P2K"
            }
        }
        ],
      "portfolio_type": "entity",
      "portfolio_id": [206],
      "start_date": "2019-12-01",
      "end_date": "2019-12-31",
      "include_online_valuations": false,
      "include_unverified": false,
      "include_deleted": true
    }
  }
}
HTTP/1.1 200


{
    "meta": {
        "columns": [
            "direct_owner",
            "security",
            "value",
            "trade_date"
        ]
    },
    "data": [
        {
            "id": "1971",
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Conversions Client",
                    "entity_id": 206
                },
                "security": {
                    "name": "11112222",
                    "entity_id": 208
                },
                "trade_date": "2019-12-31",
                "value": null
            }
        },
        {
            "id": "1970",
            "type": "transaction_query",
            "attributes": {
                "direct_owner": {
                    "name": "Conversions Client",
                    "entity_id": 206
                },
                "security": {
                    "name": "33334444",
                    "entity_id": 212
                },
                "trade_date": "2019-12-31",
                "value": null
            }
        }
    ],
    "included": [],
    "links": {
        "next": null
    }
}

Sorting

To narrow your response you can use sortings.

AttributeDescriptionExample
sortingsA transactions query sorting may be attached to a transactions query to sort rows. Accepts up to sorting 3 columns, and the order of the columns determines their priority in the request. Array of objects.

Default to trade date.
[{ "attribute": "trade_date", "ascending": false, }]

Example:
In this example, the request is first sorted by the newest trade date, then securities in alphabetical order.

POST https://examplefirm.addepar.com/api/v1/transactions/query

{
   "data":{
      "type":"transaction_query",
      "attributes":{
         "columns":["direct_owner", "security","trade_date", "id" ],
         "sorting":[
            {
               "attribute":"trade_date",
               "ascending":false
            },
            {
               "attribute":"security",
               "ascending":true
            }
         ],
         "portfolio_type":"GROUP",
         "portfolio_id":7,
         "start_date":"2021-06-30",
         "end_date":"2021-09-30",
         "include_online_valuations":true,
         "include_unverified":false,
         "include_deleted":true,
      }
   }
}
HTTP/1.1 200

{
   "meta":{
      "columns":[
         "direct_owner",
         "security",
         "trade_date",
         "id"
      ]
   },
   "data":[
      {
         "id":"1919",
         "type":"transaction_query",
         "attributes":{
            "direct_owner":{
               "name":"Integration Tester 2",
               "entity_id":188
            },
            "security":{
               "name":"Integration Trust 2",
               "entity_id":188
            },
            "trade_date":"2005-01-02",
            "transaction_id":{
               "name":"Transaction ID 2",
               "entity_id":188
            }
         }
      },
      {
         "id":"1919",
         "type":"transaction_query",
         "attributes":{
            "direct_owner":{
               "name":"Integration Tester 1",
               "entity_id":183
            },
            "security":{
               "name":"Integration Trust 1",
               "entity_id":186
            },
            "trade_date":"2005-01-01",
            "transaction_id":{
               "name":"Transaction ID 1",
               "entity_id":186
            }
         }
      }
   ],
   "included":[ ],
   "links":{
      "next":null
   }
}