Aged TPJ File

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:

hours_oldThis field quantifies the total hours that have elapsed since the inception of the first TPJ attempt.
manual_retriesThis field quantifies the number of times Bakkt has tried to pull the funds for this trade.
tpj_reasonThis field describes the reason that the TPJ failed. Note that this comes from Bakkt marketplace.
accountThis is the 8-character Client’s Account ID for the failed TPJ.
direction_from_tpj_errorThis 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_errorThis is the USD amount that Bakkt Marketplace has tried to TPJ.
amount_availableThis 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_extraThis 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.
symbolThis is the crypto symbol/coin that was executed and resulted in the failed TPJ.
sideThis is the side of the execution that resulted in the failed TPJ. (i.e., BUY/SELL)
last_priceThis is the execution price of the execution that resulted in the failed TPJ.
last_qtyThis is the quantity of coin that was executed that resulted in the failed TPJ.
notionalThis is the notional value of the execution that resulted in the failed TPJ.
execution_timeThis field describes the execution time when the order took place and then resulted in the failed TPJ.
tpj_pending_tpj_idThis 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_dateThis field is the process date for the execution that resulted in the failed TPJ.
execution_idThis 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_idThis is the 36-character account GUID for the customer transaction in the Bakkt transaction DB.
batch_transaction_idThe 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_IdThe ID that the client used to create the allocation request. This will be blank for all single trade executions.
client_order_idThis 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_positionThis is the total coin value in USD for all coins that are in the customer’s account.
record_typeTPJ update for Execution, TPJ update for allocation.

Aged TPJ Example File

242insufficient fundsXXXXWITHDRAWAL30-3SHIBUSDBUY0.00002195136674.2596810932022-03-15 14:33:15.797116+00:00XXXX20220315XXXXXXXXXXXX88
38.83insufficient fundsXXXXWITHDRAWAL52.22-2.78ETHUSDBUY2628.757166550.0019020452022-03-14 23:44:20.653214+00:00XXXX20220314XXXXXXXXXXXX0
82.85insufficient fundsXXXXWITHDRAWAL1,000.00201-799SOLUSDBUY83.2436821312.0129236810002022-03-13 03:46:27.735367+00:00XXXX20220313XXXXXXXXXXXX99
1770.40105insufficient fundsXXXXWITHDRAWAL40-4BTCUSDBUY48072.617476910.0000832142022-01-01 20:06:42.019316+00:00XXXX20220102XXXXXXXXXXXX1