The Bakkt crypto system will create an Execution and TPJ Confirm file every day. This file will be used in many reconciliation scenarios for customer trades and Third-Party Journal Cash transactions to support those trades. Additionally, you can use this file along with the Aged TPJ document to keep your system’s buying power in sync with Bakkt.

Bakkt Crypto Execution and TPJ Confirm File details:

Every day at approximately 5 pm CT a file will be available on the correspondents S3 bucket. The time period will be from 4:40 pm CT the previous day to 4:40 pm CT the current day. The Execution Confirm file has all the day’s Crypto Execution details along with the TPJ details. Any coin execution in the file can be assumed to have cleared and the coin will now be part of the customer’s position. The TPJ’s may or may not have settled depending on the account status and balance in the account.

The file name convention is straightforward.

YYYY-MM-DDexecution_and_Bakkt_tpj_confirms.

If a file is generated on 06/08/2023, the file will reflect the records as of the date in the file name.

E.g. 2023-06-08execution_and_Bakkt_tpj_confirms

At the top of the Execution Confirm file you will see a From-To time range for the report. This is meant to synchronize with the Bakkt Marketplace cash activity file.
Note that any executions/TPJ’s that happen after 4:40 Central time will show up in the next day’s file.

In the Execution Confirm file from Bakkt, you will see the following fields:

Timestamps in the file will be in ISO 8601 format
e.g. 2019-10-12T07:20:50.523Z

Field Descriptions

Fields	Description
account_id	This is the 8-character Client’s Account ID (e.g. XYZ12345).
direction	This is the direction of the net cash flow between customer account and Bakkt (Deposit or Withdrawal). For manual coin adjustments this will be blank.
side	This is the direction of the customer account’s trade. For manual cash adjustment this field will be blank. NOTE: All prices reported in this file will be positive, so you may need to adjust them using the Side values to be positive/negative based on how your system represents them.
symbol	This is the crypto symbol for the trade or for a manual coin adjustment. You will see USD for manual cash adjustments.
notional	This is the notional value of the fill (i.e. Last Price * Last Quantity). Manual cash adjustments will be the amount of the adjustment.
fees	This is the trade fee for the execution, if you charge your customers trade fees. Manual coin and cash adjustments will have blank fees. NOTE: This column will be zero if you do not charge fees, but instead use markup/markdown.
total	This is the total cost of the execution or total manual adjustment (i.e. Notional + Fees) based on side.
transaction_type	This is the category of the transaction type. They include the following: CLIENT_PURCHASE, CLIENT_SALE, CLIENT_ALLOCATION_SALE, CLIENT_ALLOCATION_PURCHASE, and MANUAL_ADJUSTMENT
CLIENT_PURCHASE	Single Buy trade execution.
CLIENT_SALE	Single Sell trade execution.
CLIENT_ALLOCATION_SALE	A Sell allocation for an account.
CLIENT_ALLOCATION_PURCHASE	A Buy allocation for an account.
MANUAL_ADJUSTMENT	- A coin adjustment transaction for the customer’s account. In this case you will see a blank for “Notional” & Payment and there will not be a TPJ transaction associated with them. - A cash adjustment transaction for the customer’s account. These will have TPJ transactions associated with them. In this case you will see a blank “Last Quantity” and “Last Price”. The “Symbol” will be “USD”.
record_type	Type of record, includes execution, allocation, TPJ update for execution, and TPJ update for allocation.
execution	Single trade execution.
allocation	Single allocation to customer.
TPJ update for execution	A previously failed TPJ that has now settled for a single trade execution.
TPJ update for allocation	A previously failed TPJ that has now settled for an allocation.
execution_time	This is the time of the execution/fill in UTC time.
tpj_time	This is the time of TPJ cash moved is settled.
client_order_id	This is the Order ID that you sent with the crypto order. This will be blank for an allocation.
execution_id	This is the Execution ID that we sent you in your execution report for the crypto execution/fill for a single trade. Our Execution IDs are often numbers but can also be alpha-numeric. Your system should treat them as text strings. It will be blank for allocations. NOTE: Be careful when using the Execution ID in Excel as it tends to try to convert the long numeric strings to Scientific Notation which will remove the granular numbers closer to what it perceives is the decimal point in the execution ID.
batch_transaction_id	The ID that the client used to create the batch of allocations that included this allocation. This will be blank for all single trade executions.
client_transaction_Id	The ID that the client used to create the allocation request. This will be blank for all single trade executions.
trade_date	This is the process date of the transaction. This will coincide with the trade date in the FIX execution report.
tpj_time_info	Not used, will be blank in files.
last_quantity	This is the quantity filled for this order fill (tied to the execution ID), allocation (tied to Client Transaction ID) or MANUAL_COIN_ADJUSTMENT. MANUAL_CASH_ADJUSTMENT will have a blank last quantity.
last_price	This is the price where the execution, allocation or MANUAL_COIN_ADJUSTMENT was filled. MANUAL_CASH_ADJUSTMENT will have a blank last price.
tpj_id	This is the TPJ ID for the request from Bakkt Marketplace. It will be the TPJ complete ID when the TPJ succeeded and the TPJ pending ID for the attempted TPJ when rejected. It will be blank when the TPJ Status is blank indicating that Bakkt's system has not yet tried to pull funds from the account due to timing of the execution fill and the run time of the report.
tpj_settled	This is the indicator if the TPJ has settled or not at Bakkt Marketplace. Values can be “completed’ Or “not_completed”.
transaction_id	Internal transaction ID.
tpj_reason	This is the explanation for a “not completed” TPJ settled status.

NOTE: The asterisk (*) indicates a conditionally-required field.

Tag	Field Name	Type	Definition	Optional/Required
	Standard Header		MsgType = 8	Required
11	`ClOrdID`	String	The client-specified unique order identifier	Required
37	`OrderID`	String	The Bakkt-generated order id returned in the acknowledgment	Required
17	`ExecID`	String	The unique identifier per ExecutionReport	Required
39	`OrdStatus`	Char	The status of the order; may include: '0' (New), '1' (PartialFill'), '2' (Filled), '8' (Rejected) , ‘3’ (DoneForDay), ‘D’ (Restated)	Required
150	`ExecType`	Char	The execution type; this value always echoes the `OrdStatus` value	Required
20	`ExecTransType`	Char	This field represents the execution transaction type; for Bakkt, this field is always '0' (for NewTrans = new transaction)	Required
55	`Symbol`	String	The Bakkt-tradeable symbol identifier from NewOrderSingle	Required
54	`Side`	Char	The market side of original order per NewOrderSingle; acceptable values include '1' (for Buy) and '2' (for Sell)	Required
44	`Price`	Float	This field is for the price (e.g., “23.0”=”23.0000”=”23”); all float fields must accommodate up to fifteen significant digits	Required
38	`OrderQty`	Float	Original order size from NewOrderSingle	*Optional
152	`CashOrderQty`	Float	Original `CashOrderQty` from NewOrderSingle	*Optional
40	`OrdType`	Char	Order type from NewOrderSingle	Required
75	`TrdDt`	String	The date with which this execution is associated	Required
60	`TransactTime`	Utctimestamp	The time that Bakkt generated the acknowledgment, fill, cancel, or rejection	Required
109	`ClientID`	String	The Bakkt-provided party identifier	Required
151	`LeavesQty`	Float	The order quantity remaining	Required
14	`CumQty`	Float	The aggregate fill quantity on this order	Required
6	`AvgPx`	Float	This field is set to fill price (set to zero on New, Cancelled, and Rejected Messages)	Required
12	`Commission`	Float	This field includes the trade fees charged on the execution; the minimum fee is charged on the first fill, and no more fees are charged until the minimum fee has been satisfied	Optional
13	`CommType`	Char	The commission type; ‘3’ – Absolute commission amount	Optional
	Standard Trailer			Required

Execution Confirm Example Report

The following two execution confirm example files will demonstrate transaction dynamics between two consecutive days:

Day 1 Example File: 2021-06-10_execution_confirms_xyyz
Day 2 Example File: 2021-06-11_execution_confirms_xyyz

In the "TPJ settled" column of the provided files, any execution marked as "not_settled" signals a failed attempt to transfer funds, impacting the account's available balance. As a result, your system must adjust the customer's buying power accordingly, reflecting the unprocessed TPJ amount, until a "settled" status confirms the completion of the transaction. This ensures accurate representation of buying power based on real-time fund availability within Bakkt's system.

For Buy Executions, the withdrawal has not been applied, so you will need to reduce the SOD amount available for this account.
For Sell Executions, the deposit has not been applied, so you will need to increase the SOD amount available for this account.

Your system will need to make these adjustments to these accounts until you get a record in the execution confirm file that says the TPJ has cleared. The Bakkt system will periodically try to clear TPJ’s that have failed on previous days (and been reported in that day’s execution confirm file). Note that your system will only get the notification of the failed TPJ in one Execution Confirm file on the day that the trade first executed and the TPJ failed. Your system should assume that it is still failed until you get a record in the Execution Confirm file that flags the execution as “settled” in the TPJ Settled column.

In order to facilitate your buying power management, Bakkt also has an Aged TPJ SOD file, that keeps track of all unsettled TPJs. This alleviates the need for your system to keep track of unsettled TPJs from day to day.

An execution that has failed on one day and then succeeded on a subsequent day will have a record with the following details:

Account ID, Execution ID and Trade Date will be the same as the original values from the Execution confirm created at the time the TPJ failed.
The Client Order ID column will be set to “MANUAL_ADJUSTMENT”
The Execution Time will be set to the timestamp where the TPJ succeeded.
The side will be set to “WITHDRAWAL” or “DEPOSIT” depending on the movement direction.
The Symbol will always be USD.
The Last Quantity, Notional and Total fields will all have the dollar amount of the TPJ that now has succeeded.
This Total field can be compared to the original Total amount for the failed TPJ amount for that Execution ID from the Execution confirm created at the time TPJ failed.
TPJ Status and TPJ Settled column will be set to “completed”.
TPJ ID will be the TPJ completed ID that Bakkt’s systems will know.

When you get this record, your system will know that this historical failed TPJ for the stated Execution ID has now cleared. That means you can use the account, side and Total amount to reconcile and your system can stop adjusting the brokerage account’s SOD Amount Available as described above.

Note that from time to time, Bakkt's operations team may need to perform manual adjustments to positions and/or cash. In the case of a cash adjustment most of the fields will be the same as above, but both the Client Order ID and the Execution ID will be set to “MANUAL_ADJUSTMENT" as seen in the Day 2 example. The amount in this Manual TPJ confirm will also be included in the SOD Amount Available. In the case of a coin adjustment, the record will not currently show up in the report. This is a feature request awaiting prioritization since it doesn’t happen that frequently.

The sample report lists transactions between two specific points in time. In Day 1 example, 'From' date of June 9, 2021, at 00:15 UTC, marks when the reporting period begins, and the 'To' date of June 10, 2021, at 00:15 UTC, indicates when it ends. This 24-hour window is the timeframe during which all listed activities and transactions were recorded and compiled into the report.

Day 1 Example File:

From:2021-06-09T00:15:00Z
To: 2021-06-10T00:15:00Z

Account ID	Client Order ID	Execution ID	Trade Date	Execution Time	Side	Symbol	Last Quantity	Last Price	Notional	Total	TPJ Status	TPJ ID	TPJ Settled
YYZ08849	1296023955039	21060921000862660	2021-06-09	2021-06-09T14:25:01Z	BUY	BTCUSD	0.00028445	35155.43	10.00	10.00	completed	e9b98741-5739-4713-aa0d-aa06c14d7d2e	completed
YYZ08879	1292006035039	1060921523857408	2021-06-09	2021-06-09T12:26:16Z	BUY	BTCUSD	0.0000283	35341.881976	1.00	1.00	completed	130d6dd8-a573-4293-891d-60ffc0b4d105	completed
YYZ07972	1292084475039	1060917692491776	2021-06-09	2021-06-09T07:35:47Z	BUY	LTCUSD	1	161.3778087	161.38	161.38,	rejected	04f4c92b-aaef-49e5-8507-a14e75ff8fe7	not_settled
YYZ12946	1292084855039	1060916867393536	2021-06-09	2021-06-09T06:29:02Z	BUY	ETHUSD	0.59274392	2530.6037886	1500.00	1500.00			not_settled

Day 2 Example File:

From: 2021-06-10T00:15:00Z
To: 2021-06-11T00:15:00Z

Account ID	Client Order ID	Execution ID	Trade Date	Execution Time	Side	Symbol	Last Quantity	Last Price	Notional	Fees	Total	TPJ Status	TPJ ID	TPJ Settled
YYZ08849	1296023955039	21060921000862660	2021-06-10	2021-06-10T14:25:01Z	BUY	BTCUSD	0.00028445	35155.43	10.00	0.00	10.00	completed	e9b98741-5739-4713-aa0d-aa06c14d7d2e	completed
YYZ08879	1292006035039	1060921523857408	2021-06-10	2021-06-10T12:26:16Z	BUY	BTCUSD	0.0000283	35341.881976	1.00	0.00	1.00	completed	130d6dd8-a573-4293-891d-60ffc0b4d105	completed
YYZ07972	MANUAL_ADJUSTMENT	1060917692491776	2021-06-09	2021-06-10T06:30:00Z	WITHDRAWAL	USD	161.38		161.38		161.38	completed	04f4c92b-aaef-49e5-8507-a14e75ff8fe7	completed
YYZ12946	MANUAL_ADJUSTMENT	1060916867393536	2021-06-09	2021-06-10T06:30:00Z	WITHDRAWAL	USD	1500.00		1500.00		1500.00	completed	b9060fba-a6f6-4734-a59d-2717bccf6994	completed
YYZ12946	MANUAL_ADJUSTMENT	MANUAL_ADJUSTMENT	2021-06-10	2021-06-10T06:31:00Z	WITHDRAWAL	USD	200.00		200.00		200.00	completed	2554aee9-a422-49fb-a934-b87f9ccef87e	completed