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
: Success400 Bad Request
: Improperly formatted query or lacking necessary data permissions403 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
}
}
Updated 4 days ago