Bakkt will place an Aged TPJ file into client’s S3 buckets that will contain the full list of TPJ’s that have failed and continue to fail. This file will contain ALL of the unsuccessful TPJ’s that your system needs to use to adjust buying power.

For clients utilizing Bakkt's fiat onramp service, we call for cash using a mechanism called Third Party Journal (TPJ). After a trade has been filled, the Bakkt system will call for cash to satisfy the trade. If that call for cash fails, our system will flag it as “Not Settled” in the Execution Confirm file. These unsettled transactions can happen due to the timing of fund availability (e.g., ACH (Automated Clearing House) sent from bank, but not yet received, Funds from a sell equity trade have not settled, etc.). They can also happen if the user was allowed to trade without having funds available. It is important that your operations team review these aged TPJ’s to resolve them in a timely manner. The Bakkt system will retry these unsettled trades twice a day at 6:00 AM CT and 5:00 PM CT and they will settle if the money comes into the account.

Twice a day, Bakkt will generate an Aged TPJ file that will include all the unsettled TPJ’s for a client’s customers. The aged TPJ reports will be generated at 9:30 AM CT and 7:30 PM CT. They will be placed in the client’s S3 bucket under the sub directory named tpj_aging. This file should be used by your operations team to identify the failed cash calls for trades so you can identify the cause and resolve the failed payment. Any unsettled trades that exist after 30 days will be written off from the client’s portion of the revenue share in the subsequent month.

In addition, there is an Official SOD Bakkt TPJ aging file that is put into the sub directory named sod_tpj_aging. You will find an official tpj aging file in your S3 bucket at approximately 5PM CT. This will run at the same time as your execution confirm file, described in that document. Use the Official SOD Bakkt TPJ aging file to adjust customer buying power based on these unsettled trades.

The file name convention for the SOD file:

sodtpj_aging/aged_bakkt_tpj{correspondant_code}_YYYYMMDD.csv

The file name convention for the unsettled TPJ file generated twice daily:

tpjaging/aged_bakkt_tpj{correspondant_code}_YYYYMMDD-HHMMSSUTC.csv

Timestamps in the file will be in ISO 8601 format:

e.g. 2019-10-12T07:20:50.523Z

In the Aged TPJ file you will see the following fields:

Fields	Description
hours_old	This field quantifies the total hours that have elapsed since the inception of the first TPJ attempt.
manual_retries	This field quantifies the number of times Bakkt has tried to pull the funds for this trade.
tpj_reason	This field describes the reason that the TPJ failed. Note that this comes from Bakkt marketplace.
account	This is the 8-character Client’s Account ID for the failed TPJ.
direction_from_tpj_error	This field describes the direction of TPJ money movement that failed. WITHDRAWAL means that the customer bought and owes money for that crypto purchase. DEPOSIT means that the customer sold and Bakkt owes the customer money for that crypto sale. Deposits typically will only fail if the account has been closed at Bakkt for some reason.
amount_from_tpj_error	This is the USD amount that Bakkt Marketplace has tried to TPJ.
amount_available	This is the USD amount that the Bakkt Marketplace TPJ service thinks is currently available for TPJ in the customer’s account at Bakkt marketplace. The value will be set to 0 initially. This will be addressed in an upcoming release. Do not use the value in the column.
amount_extra	This field is the calculation (amount from tpj error - amount available) using the above columns. Do not use the value in the column. This will be addressed in an upcoming release.
symbol	This is the crypto symbol/coin that was executed and resulted in the failed TPJ.
side	This is the side of the execution that resulted in the failed TPJ. (i.e., BUY/SELL)
last_price	This is the execution price of the execution that resulted in the failed TPJ.
last_qty	This is the quantity of coin that was executed that resulted in the failed TPJ.
notional	This is the notional value of the execution that resulted in the failed TPJ.
execution_time	This field describes the execution time when the order took place and then resulted in the failed TPJ.
tpj_pending_tpj_id	This is an internal field used by Bakkt to know the ID that was used when trying to pull cash from the Bakkt TPJ service.
trade_date	This field is the process date for the execution that resulted in the failed TPJ.
execution_id	This field is the execution ID for the execution that resulted in the failed TPJ. Note that Excel can corrupt this number when it is all numeric. We plan to put a character at the beginning of these numbers to prevent that behavior.
customer_transaction_id	This is the 36-character account GUID for the customer transaction in the Bakkt transaction DB.
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.
client_order_id	This is the original Client Order ID that the client system sent which resulted in the execution and then resulted in the failed TPJ.
estimated_account_position	This is the total coin value in USD for all coins that are in the customer’s account.
record_type	TPJ update for Execution, TPJ update for allocation.

Aged TPJ Example File

hours_old	manual_retries	tpjd_reason	account	direction_from_tpj_error	amount_from_tpj_error	amount_available	amount_extra	symbol	side	last_price	last_qty	notional	execution_time	tpj_pending_tpj_id	trade_date	execution_id	customer_transaction_id	client_order_id	estimated_account_position
24	2	insufficient funds	XXXX	WITHDRAWAL	3	0	-3	SHIBUSD	BUY	0.00002195	136674.25968109	3	2022-03-15 14:33:15.797116+00:00	XXXX	20220315	XXXX	XXXX	XXXX	88
38.8	3	insufficient funds	XXXX	WITHDRAWAL	5	2.22	-2.78	ETHUSD	BUY	2628.75716655	0.00190204	5	2022-03-14 23:44:20.653214+00:00	XXXX	20220314	XXXX	XXXX	XXXX	0
82.8	5	insufficient funds	XXXX	WITHDRAWAL	1,000.00	201	-799	SOLUSD	BUY	83.24368213	12.01292368	1000	2022-03-13 03:46:27.735367+00:00	XXXX	20220313	XXXX	XXXX	XXXX	99
1770.40	105	insufficient funds	XXXX	WITHDRAWAL	4	0	-4	BTCUSD	BUY	48072.61747691	0.00008321	4	2022-01-01 20:06:42.019316+00:00	XXXX	20220102	XXXX	XXXX	XXXX	1