Query

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
Endpoints	POST /v1/transactions/query
Produces	JSON
Pagination	No
Application Permissions Required	"API Access: Create, edit, and delete" "Transaction firm view: View only" and "Transactions: View-only" to extract view data.
OAuth Scopes	`TRANSACTIONS` 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:

Parameter	Description	Example
`column`	List of column attribute keys. [String].	`["trade_date", "security"]`
`start_date`	The start date of the time period of transaction data. String. "YYYY-MM-DD"	`"2021-09-05"`
`end_date`	The end date of the time period of transaction data. String. "YYYY-MM-DD"	`"2021-09-06"`
`portfolio_type`	The 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_ids`	External id of the entity/group being queried for. Array of Objects.	`[{ "external_type_key": "salesforce", "external_id": "salesforce_external_id" }]`

Transaction Columns

Column	Appears in Addepar As
`account_number`	Account #
`accrued_income`	Snapshot Accrued Income
`are_tax_lots_ignored`	Are Custodian Tax Lots Ignored
`asset_class`	Asset Class
`cash_account`	Paid to(from)
`client`	Client
`comments`	Comments
`context`	Context
`currency`	Currency
`cusip`	CUSIP
`data_source`	Data Source
`data_type`	Summary Data Level
`description`	Description
`direct_owner_id`	Direct Owner Entity ID
`edited_online_data`	Edited Online Data
`associated_hidden_positions`	Associated Hidden Position(s)
`entity_id`	Entity ID
`ex_date`	Ex-Date
`fees`	Fees
`fund`	Fund
`affected_by_hidden_positions`	Affected by Hidden Position(s)
`has_tax_lots`	Has Tax Lots
`has_wash_sale`	Has Wash Sale
`is_verified`	Is Verified
`direct_owner`	Direct Owner
`id`	ID
`isin`	ISIN
`limited_partner`	Limited Partner
`other_affected_assets`	Other Affected Assets
`ownership_type`	Ownership Type
`account_name`	Account Name
`position`	Position
`position_id`	Position ID
`posted_date`	Posted Date
`price_factor`	Price Factor
`price_per_share`	Price Per Share
`recallable_amount`	Recallable Amount
`security`	Security
`model_type`	Security Model Type
`sedol`	SEDOL
`grouping_configuration`	Grouping Configuration
`tag`	TAG
`ticker_symbol`	Ticker Symbol
`trade_date`	Trade Date
`settlement_date`	Settlement Date
`snapshot_units`	Snapshot Units
`summary`	Summary
`transaction_units`	Transaction Units
`type`	Type
`units`	Units
`units_changed`	Units Changed
`value`	Value
`vendor_id`	Vendor ID
`affects_adjusted_value`	Affects Adjusted Value
`affects_cost_basis`	Affects Cost Basis
`affects_paid_in_capital`	Affects Paid-in Capital
`affects_unfunded_commitments`	Affects Unfunded Commitments
`general_fee`	General Fee
`external_brokerage_fee`	External Broker Fee
`internal_brokerage_fee`	Internal Broker Fee
`other_government_tax`	Other Government Tax
`counterparty_fee`	Counterparty Fee
`entry_exit_fee`	Entry and Exit Fee
`foreign_fee`	Foreign Fee
`matching_fee`	Matching/Confirmation Fee
`market_fee`	Market Fee
`market_tax`	Market Tax
`other_fee`	Other Fee
`stamp_tax`	Stamp Duty
`stock_exchange_tax`	Stock Exchange Tax
`stock_exchange_fee`	Stock Exchange Fee
`turnover_fee`	Turnover Fee
`value_added_tax`	Value Added Tax (VAT)
`withholding_tax`	Withholding Tax
`last_edit_by`	Last Edited By
`last_edit_date`	Last Edited Date
`modified_date`	Modified Date
`general_distribution`	General Distribution
`return_of_capital`	Return of Capital
`long_term_capital_gain`	Long-Term Capital Gain
`short_term_capital_gain`	Short-Term Capital Gain
`unknown_capital_gain`	Unknown Capital Gain
`interest_income`	Interest Income
`ordinary_income`	Ordinary Income
`dividend_income`	Dividend Income
`total_distribution`	Total Distribution
`created_date`	Created 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:

Parameter	Description	Example
`filters`	A 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" }]`
`sortings`	A 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 }]`
`limit`	The max number of transactions returned by the query. Number. Default to null (No limit, returns everything unless request times out.)	`500`
`include_online_valuations`	Whether online snapshots should be returned by the query. Boolean. Default to false.	`true`
`include_unverified`	Whether unverified transactions should be returned by the query. Boolean. Default to false.	`true`
`include_deleted`	Whether 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:

Parameters	Description	Example
attribute	The instance of the portfolio query attribute to be filtered on. String.	`"start_date"`
operator	Specifies the operation of the filter. String. Supported Values: - `include` - `exclude`	`"include"`
type	Specifies 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.
date	Required 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"}`
number	Required 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.

Parameters	Details	Example
`type`	Specifies 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"`
`start`	Required for `static_time_period`. String. "YYYY-MM-DD"	`"2021-01-01"`
`end`	Required for `static_time_period`. String. "YYYY-MM-DD"	`"2021-12-30"`
`period`	Required for `relative_time_period`and `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.

Attribute	Description	Example
sortings	A 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
   }
}