Jay Data Format (v1.11)
Jay Data Format consists of the following file formats:
🅣 TRANSACTION CSV FILE FORMAT
🅟 POSITIONS CSV FILE FORMAT
JSON format is also available for both files on request.
Please contact data@jaysolutions.com if you have further questions about Jay Data Format.
🅣 Transactions File Format
Fields for Transactions
Field | Required | Format | Description |
|---|---|---|---|
senderId | 3-50 characters | Sender's identifier (e.g. company name, company code) | |
customerId | ✔️ | 1-50 characters | Sender's identifier for end customer |
portfolioId | ✔️ | 1-50 characters | Sender's identifier for customer's portfolio |
cashAccount | ✔️ | 1-50 characters | Cash account id linked to portfolio (e.g. IBAN, account number, account code) |
cashAccountBIC | 8-11 characters | Cash management Bank BIC | |
tradeDate | ✔️ | YYYY-MM-DD | Date when transaction is executed. In dividends: ex date. |
paymentDate | ✔️ | YYYY-MM-DD | Date when cash movement takes place |
inputDate | ✔️ | YYYY-MM-DD | Date when transaction is inserted to the system |
uniqueId | ✔️ | 1-50 characters | Transaction unique reference. Use same reference if transaction details are updated. |
transactionType | ✔️ | 1-50 characters | Indication of transaction type. See transaction types. |
transactionSubType | 0-50 characters | Voluntary transaction sub type that can give more information on the transaction type. Not used, reserved for future use. | |
bloombergTicker | (✔️) At least one of the alternative security identifiers is required | 0-255 characters | Bloomberg ticker |
securityCode | (✔️) At least one of the alternative security identifiers is required | 0-255 characters | Generic code that identifies the instrument, if Cash balance use code: CASH |
isin | (✔️) At least one of the alternative security identifiers is required | 12 characters | Two letter country code - a nine character alphanumeric set - a single check digit |
securityName | ✔️ | 1-255 characters | Security name from sender's system |
mic | ISO 10383 CODE | MIC Code for trading Venue. Should be supplied when information is available. https://www.iso20022.org/market-identifier-codes | |
securityCurrency | ✔️ | ISO 4217 CODE | Security currency https://en.wikipedia.org/wiki/ISO_4217 |
fxRate | ✔️ | /^\d+(\.\d{6})?$/ | FX rate 6 decimals. If same as portfolio currency use 1,000000 |
quantity | ✔️ | numeric | Quantity or nominal of the trade. For dividends or interest, the position of the security for which the proceed is payed. |
unitPriceInSecurityCurrency | ✔️ | numeric | Unit price in security currency. For dividends, dividend per unit. For interest, interest percentage as per payment cycle. |
valueInSecurityCurrency | ✔️ | numeric | Gross value in security currency -> Quantity * Unit price |
valueInPortfolioCurrency | ✔️ | numeric | Gross value in portfolio currency -> Quantity * Unit price */ fx rate |
brokerageFeeInSecurityCurrency | numeric | Broker fees and any similar costs | |
otherFeeInSecurityCurrency | numeric | Transaction fees etc. trade related cost (other than broker fee) in security currency. For example delivery costs. | |
accruedInterestInSecurityCurrency | numeric | Accrued interest in security currency – Mandatory for bonds | |
taxInSecurityCurrency | numeric | Applicable taxes. For example for dividends the amount of tax at source. | |
taxType | 0-20 characters | Value Added Tax (VAT), Withholding Tax (WITHHOLDING_TAX), Tax at Source (TAX_AT_SOURCE), Transaction Tax (TRANS_TAX) | |
totalValueInSecurityCurrency | ✔️ | numeric | Net value of the trade. For interest and dividends net payment in security currency |
totalValueInPortfolioCurrency | ✔️ | numeric | Net value of the trade. For interest and dividends net payment in portfolio currency |
info | 0-50 characters | Additional information regarding transaction | |
tradeReference | 0-50 characters | Use uniqueId field for regular transactions. This is used for special purposes e.g. taxation. When used, will override uniqueId and relatedId for transactions and cancellations. | |
relatedId | 0-50 characters | Used to refer to the original transaction id when e.g. canceling transactions or combining FX trades | |
traderBIC | 0-50 characters | The BIC code of the trader | |
counterPortfolioId | 0-50 characters | Sender's identifier for portfolio used by some transaction types | |
portfolioCurrency | ✔️ | ISO 4217 CODE | Security currency https://en.wikipedia.org/wiki/ISO_4217 |
Transaction Type Options
Type | Description |
|---|---|
BUY | Buy indicator (shares, bonds, etfs, funds etc.) |
SELL | Sell indicator (shares, bonds, etfs, funds etc.) |
INSERT_TO_PORTFOLIO | Used when inserting transactions to portfolio without cash effect |
DIVIDEND | Dividend indicator (shares, etfs, funds etc.) |
WITHDRAWAL_FROM_PORTFOLIO | Used when exporting transactions from portfolio without cash effect |
COUPON | COUPON for Bond coupons. |
TRAILER_FEE | Used when fund credits trailer fees to end clients |
CAPITAL_CALL | Private equity fund calls for Capital |
CAPITAL_VALUE_CHANGE | Update to Private equity funds NAV |
EXCHANGE | Exchange can be used in Mergers and Demergers. |
CAPITAL_PROFIT | Profit for Private equity fund (distribution) |
DIVIDEND_AS_SHARE | Dividend paid as own or other company shares |
CAPITAL_RETURN | Capital return for Private equity funds |
EXPIRATION | Expiration for bonds |
AMORTIZATION | Amortization for bonds (use EXPIRATION instead) |
CAPITAL_COST | Capital cost for Private equity funds (contribution) |
TRAILER_FEE_SUBSCRIPTION | Automatic Fund subscription by fund company with paid Trailer fee |
CAPITAL_COMMITMENT | Committed capital to Private equity funds |
CAPITAL_RETURN_RECALLABLE | Callable capital return for Private equity funds |
SPLIT | Security split |
SUBSCRIPTION | Subscription of a fund |
CAPITAL_CALL_REVERSAL | Reversal of capital call for Private equity fund |
CAPITAL_COST_REVERSAL | Reversal of capital cost for Private equity fund |
CAPITAL_COMMITMENT_REVERSAL | Reversal of capital commitment for Private equity fund |
CAPITAL_PROFIT_REVERSAL | Reversal of capital profit for Private equity fund |
CAPITAL_RETURN_REVERSAL | Reversal of capital return for Private equity fund |
CAPITAL_PROFIT_RECALLABLE | Callable capital profit for Private equity funds |
EXCHANGE_IN_EMISSION | Not applicable. Can be informed by using the code EXCHANGE |
CANCEL_TRANSACTION | Use when Cancelling already booked transaction. Please remember to use the relatedId field when using this type, so the transaction to be cancelled can be recognized. |
REDEMPTION | Redemption of a fund |
SELL_TO_CUSTOMER | Used in special cases |
BUY_FROM_CUSTOMER | Used in special cases |
SWITCH_OLD | Used when switching from one fund to another or between the fund classes of a single fund. Source instrument. |
SWITCH_NEW | Used when switching from one fund to another or between the fund classes of a single fund. Target instrument. |
SELL_FEE | Shares sold as fees to management company e.g. insurance company |
FUTURE_VARIATION_MARGIN | Daily futures variation margin change (negative and positive values) |
Cash Related Transaction Types | |
CASH_OUT | Used when debiting cash account. For example client makes debit from cash management account |
INTEREST | INTEREST for cash account related interests |
CASH_IN | Used when crediting cash account. For example client makes credit to cash management account |
SERVICE_FEE | Service fee for example from the cash management bank |
BANKING_FEE | Banking fee for example from the cash management bank |
CUSTODY_FEE | Custody Fee or other similar cost debited from account |
ASSET_MANAGEMENT_FEE | Asset Management Fee |
FEE | Other fees or costs applicable |
PROFIT | Profit from e.g. rentals |
FX_TRADE_IN | Bought FX currency |
FX_TRADE_OUT | Sold FX currency |
FX_FORWARD_IN | Bought FX forward |
FX_FORWARD_OUT | Sold FX currency for forward |
Example Transactions CSV
senderId;customerId;portfolioId;cashAccount;cashAccountBIC;tradeDate;paymentDate;inputDate;uniqueId;transactionType;transactionSubType;isin;bloombergTicker;securityCode;securityName;mic;securityCurrency;fxRate;quantity;unitPriceInSecurityCurrency;valueInSecurityCurrency;valueInPortfolioCurrency;brokerageFeeInSecurityCurrency;otherFeeInSecurityCurrency;accruedInterestInSecurityCurrency;taxInSecurityCurrency;taxType;totalValueInSecurityCurrency;totalValueInPortfolioCurrency;info;tradeReference;relatedId;traderBIC;counterPortfolioId;portfolioCurrency
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-01;2019-09-03;2019-09-01;2275267JAX1;BUY;;IE00B74DQ490;IBC9 GY Equity;IBC9 GY Equity;iShares Global High Yield Corporate Bond;XETR;EUR;1.000000;3500.000000;84.685400;296398.900000;296398.090000;148.200000;;;;;296547.1;296547.1;ETF strategy Buy;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-12;2019-09-30;2019-09-12;2275267JAX2;DIVIDEND;;IE00B74DQ490;IBC9 GY Equity;IBC9 GY Equity;iShares Global High Yield Corporate Bond;XETR;EUR;1.000000;3500.000000;2.422714;8479.500000;8479.500000;;;;1271.930000;TAX_AT_SOURCE;7207.57;7207.57;Tax at source 15%;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.USD;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX3;BUY;;US9128282F67;;US9128282F67USD;US Treasury N/B 1.125% 31.8.2021;NA;USD;1.109850;7900000.000000;98.785156;7804027.324000;7031605.480000;;;4150.760000;;;7808178.1;7035345.41;Bond Mandate ;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.USD;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX4;SELL;;IE00BQQ3Q067;DEMD LN Equity;IE00BQQ3Q067;Wisdom Tree EM Eq Income UCITS ETF;XETR;USD;1.110222;15000.000000;15.044000;225660.000000;203260.670000;676.980000;;;;;224983.02;202650.89;;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.USD;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX5;AMORTIZATION;;US654902AB18;;;Nokia OYJ (NOKIA 5 3/8 05/15/19);BMTF;USD;1.122200;1000000.000000;100.000000;1000000.000000;891106.750000;;;;;;1000000;891106.75;Amortization of Nokia Bond;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX6;COUPON;;FI4000278536;;;HKScan 2.625% 21.9.2022;BMTF;EUR;1.000000;250000.000000;2.625000;6562.500000;6562.500000;;;;;;6562.5;6562.5;Coupon 2.655% 2019;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX7;TRAILER_FEE;;FI4000223573;AKTNMCC FH Equity;;Aktia Nordic Micro Cap C;;EUR;1.000000;584.890000;1.000000;584.890000;584.890000;;;;;;584.89;584.89;Q3 Trailer Fee;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX8;TRAILER_FEE_SUBSCRIPTION;;FI0008802889;FON2KPA FH Equity;;Fondita 2000+A;;EUR;1.000000;44.392200;29.685169;1317.790000;1317.790000;;;;;;1317.79;1317.79;Trailer fee - subscription to new units;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-01;2019-09-30;2019-09-28;2275267JAX9;ASSET_MANAGEMENT_FEE;;;;Asset Management fee;Asset Management fee;;EUR;1.000000;4000.000000;1.000000;4000.000000;4000.000000;;;;960.000000;VAT;4960;4960;Q3 2019 Asset Management Fee;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-01;2019-09-30;2019-09-28;2275267JAX10;SAFEKEEPING_FEE;;;;Safekeeping fee;Safekeeping fee;;EUR;1.000000;27.600000;1.000000;27.600000;27.600000;;;;;;27.6;27.6;Q3 Custody Fee;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-01;2019-09-30;2019-09-28;2275267JAX11;SERVICE_FEE;;;;Service fee;Service fee;;EUR;1.000000;5.500000;1.000000;5.500000;5.500000;;;;;;5.500000;5.500000;Q3 Bank Fees ;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-01;2019-09-30;2019-09-28;2275267JAX12;FEE;;;;Fee;Fee;;EUR;1.000000;2.800000;1.000000;2.800000;2.800000;;;;;;2.800000;2.800000;Withdrawal/Deposit Fees;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-30;2275267JAX13;BUY;;FI0009007066;RAMI FH Equity;RAMI FH Equity;Ramirent Ltd;XHEL;EUR;1.000000;372905.000000;1.000000;3356145.000000;3356145.000000;;;;;;3356145.000000;3356145.000000;Sell - Purhase offer;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-30;2275267JAX14;CASH_IN;;;;CASH;Cash;;EUR;1.000000;50000.000000;1.000000;50000.000000;50000.000000;;;;;;50000.000000;50000.000000;Deposit;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-30;2275267JAX15;CASH_OUT;;;;CASH;Cash;;EUR;1.000000;154345.300000;1.000000;154345.300000;154345.300000;;;;;;154345.300000;154345.300000;Withdrawal;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.EUR;BANKFIBIC;2019-09-28;2019-09-30;2019-09-30;2275267JAX16;BUY;;;;FX;FX;;USD;1.123456;1000000.000000;1.000000;10000000.000000;890110.52;;;;;;10000000.000000;890110.52;FX Trade USD/EUR;;;;;EUR
ASSETMANAGER1;121212;121212.1;121212.USD;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX17;CANCEL_TRANSACTION;;US9128282F67;;US9128282F67USD;US Treasury N/B 1.125% 31.8.2021;NA;USD;1.109850;7900000.000000;98.785156;7804027.324000;7031605.480000;;;4150.760000;;;7808178.1;7035345.41;Bond Mandate ;;2275267JAX3;;;EUR
ASSETMANAGER1;121212;121212.1;121212.USD;BANKFIBIC;2019-09-28;2019-09-30;2019-09-28;2275267JAX3;BUY_FROM_CUSTOMER;;US9128282F67;;US9128282F67USD;US Treasury N/B 1.125% 31.8.2021;NA;USD;1.109850;7900000.000000;98.785156;7804027.324000;7031605.480000;;;4150.760000;;;7808178.1;7035345.41;Bond Mandate ;;;;212121.2;EUR🅟 Positions File Format
Fields for Positions
Field | Required | Format | Description |
|---|---|---|---|
senderId | 3-50 characters | Sender's code (company name or company code) | |
customerId | ✔️ | 1-50 characters | Sender's code for customer |
portfolioId | ✔️ | 1-50 characters | Sender's code for customer's portfolio |
positionDate | ✔️ | YYYY-MM-DD | Date of position |
bloombergTicker | (✔️) At least one of the alternative security identifiers is required | 0-255 characters | Bloomberg ticker |
securityCode | (✔️) At least one of the alternative security identifiers is required | 0-255 characters | Generic code that identifies the instrument, if Cash balance use code: CASH |
isin | (✔️) At least one of the alternative security identifiers is required | 12 characters | Two letter country code - a nine character alphanumeric set - a single check digit |
cashAccount | (✔️) Required if cash | 0-50 characters | Cash account id linked to portfolio (IBAN, account number, account code) |
securityName | ✔️ | 0-255 characters | Security name from sender's system |
mic | ISO 10383 CODE | MIC Code for trading Venue. Should be supplied when information is available. https://www.iso20022.org/market-identifier-codes | |
position | ✔️ | numeric | Position/nominal. Security or cash holdings on position date |
unitPriceInSecurityCurrency | ✔️ | numeric | Unit price in security currency |
marketValueInPortfolioCurrency | ✔️ | numeric | Closing market value in portfolio currency |
purchaseValueInPortfolioCurrency | ✔️ | numeric | Purchase value in portfolio currency |
marketValueInSecurityCurrency | ✔️ | numeric | Closing market value in security currency |
purchaseValueInSecurityCurrency | ✔️ | numeric | Purchase value in security currency |
accruedInterestInSecurityCurrency | numeric | Accrued interest in security currency – mandatory for bonds | |
securityCurrency | ✔️ | ISO 4217 CODE | Currency of security (SEK, USD, etc). https://en.wikipedia.org/wiki/ISO_4217 |
cashFlowInboundInPortfolioCurrency | numeric | Inbound cash flow is used to calculate returns in certain reporting scenarios | |
cashFlowOutboundInPortfolioCurrency | numeric | Outbound cash flow is used to calculate returns in certain reporting scenarios. Report negative value. | |
portfolioCurrency | ✔️ | ISO 4217 CODE | Currency of security (SEK, USD, etc). https://en.wikipedia.org/wiki/ISO_4217 |
Example Positions CSV
senderId;customerId;portfolioId;positionDate;isin;bloombergTicker;securityCode;cashAccount;securityName;position;unitPriceInSecurityCurrency;marketValueInPortfolioCurrency;purchaseValueInPortfolioCurrency;marketValueInSecurityCurrency;purchaseValueInSecurityCurrency;accruedInterestInSecurityCurrency;securityCurrency;cashFlowInboundInPortfolioCurrency;cashFlowOutboundInPortfolioCurrency;portfolioCurrency
ASSETMANAGER1;121212;121212.1;2019-09-30;LU1834986900;SX7EEX GY Equity;SX7EEX GY Equity;FI1410093000123458;Lyxor STOXX Europe 600 Healthcare ETF;1895;100.520000;190485.400000;170217.760000;190485.400000;170217.760000;;EUR;;;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;;;71402000129;FI1410093000123458;OP-High Yield America A (growht);10646.6627;102.860000;1095115.730000;1052413.190000;1095115.730000;1052413.190000;;EUR;4243.000000;-10256.000000;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;DE0006289309;POHFINB FH Equity;POHFINB FH Equity;FI1410093000123458;iShares EURO STOXX BANKS 30-15 UCITS ETF DE;9858;7.871000;77592.320000;106015.740000;77592.320000;106015.740000;;EUR;;;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;FI4000224365;MLLEUA2 LX Equity;MLLEUA2 LX Equity;FI1410093000123458;OP-America A ;1282.5943;109.309998;140200.380000;134185.020000;140200.380000;134185.020000;;EUR;;;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;IE00BV8C9B83;IE00BV8C9B83 Corp ISIN;IE00BV8C9B83 Corp ISIN;FI1410093000123458;Ireland Government Bond (IRISH 1.7 05/15/37);1472.5433;117.210000;172596.800000;162848.570000;172596.800000;162848.570000;16547.130000;EUR;;;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;FI0008800206;OPJAPSA FH Equity;OPJAPSA FH Equity;FI1410093000123458;OP-Finland A;672.7514;296.899999;199739.890000;217831.120000;199739.890000;217831.120000;;EUR;;;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;LU0278470058;OPHYAMA FH Equity;OPHYAMA FH Equity;FI1410093000123458;BGF Emg Mkts Local Ccy Bond A2 USD;5560.91;21.366438;131126.264624;131928.493536;118816.840000;119543.760000;;USD;;;EUR
ASSETMANAGER1;121212;121212.1;2019-09-30;;OPVUOKT FH Equity;;FI1410093000123458;OP-Rental Yield B;17337.1612;127.050000;2202686.330000;2076620.900000;2202686.330000;2076620.900000;;EUR;;;EUR