Query (Beta)

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.

📘
Beta Feature
The base route /v1/transactions/query and all corresponding endpoints described herein are in the Addepar Beta Program. All beta features described herein are provided “as is'” and “as available” with no warranty or guarantee of functionality and may be modified or removed at any time by Addepar.
Please send feedback or questions to your Addepar contact.


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
   }
}