Some traders want to see their current open trade equity (OTE) and closed out trades (P&L) based on yesterday’s settlement, not the original price when the trade was opened. In DOMTrader's Trading Preferences, under Trading Display, you can set the OTE and P&L calculation to use the previous settlement (here highlighted with a red box).
The RTD calls in Excel pull OTE and P&L using yesterday’s settlement:
=RTD("cqg.rtd", ,"OrderData","Symbol", "", "OpenTradeEquity","AccountNumber")
=RTD("cqg.rtd", ,"OrderData","Symbol", "", "ProfitLoss","AccountNumber")Price to Dollar
If you want CQG to use the entry price but in Excel you want to base the OTE and P&L on yesterday’s settlement, this can be accomplished using a CQG function called Price to Native Currency (P2D). P2D converts price units to US dollars using the current contract scale. For example, this RTD formula using “Close,” which is the same as current price, returns the current actual dollar value of the corn contract (symbol: ZCE). Corn trades in cents per bushel and there are 5,000 bushels; therefore, 379.25 * 5,000 = $18,962.50.
=RTD("cqg.rtd", ,"ContractData", "PriceToDollar(ZCE)", "Close")This next RTD formula returns the current dollar value of the contract based on yesterday’s settlement:
=RTD("cqg.rtd", ,"ContractData", "PriceToDollar(ZCE)", "Y_Settlement")Now, if you take the difference between today’s current dollar value of the contract and subtract yesterday’s contract dollar value based on yesterday’s settlement, you have the net change based in dollars for one contract. Simply, multiply that by the number of open contracts in your account:
=RTD("cqg.rtd", ,"OrderData", "Symbol", "", "OpenPosition","AccountNumber")The P2D function can also determine the dollar value of the tick size:
=RTD("cqg.rtd",,"ContractData","PriceToDollar(Tsize(ZCE))","LastQuoteToday")An additional function is the tick size:
=RTD("cqg.rtd",,"ContractData","Tsize(ZCE)","LastQuoteToday",,"T")If we determine the inverse of the tick size, we know how many ticks are in one point, and if we multiply that by the dollar value of the tick size, we get the dollar value of a one-point move:
=1/RTD("cqg.rtd",,"ContractData","Tsize(ZCE)","LastQuoteToday",,"T")*RTD("cqg.rtd",,"ContractData","PriceToDollar(Tsize(ZCE))","LastQuoteToday")If you want to determine the gain or loss of a closed-out trade relative to yesterday’s settlement, you have to know the exit price of the trade (RTD does not pull in fill prices) and multiply the exit price by the above RTD formula to determine the dollar value of the contract based on the exit price. For example, 379.25 (this is actually $3.7925).
=379.25*1/RTD("cqg.rtd",,"ContractData","Tsize(ZCE)","LastQuoteToday",,"T")*RTD("cqg.rtd",,"ContractData","PriceToDollar(Tsize(ZCE))","LastQuoteToday")Subtract this from the P2D RTD formulas using yesterday’s settlement and multiply by the number of filled contracts in the orders. For filled sell orders or filled buy orders use:
=RTD("cqg.rtd", ,"OrderData", "Symbol", "", "FilledSellOrders", "AccountNumber")
=RTD("cqg.rtd", ,"OrderData", "Symbol", "", "FilledBuyOrders", "AccountNumber")Make sure to lower your Excel RealTimeData (RTD) throttle to 50 milliseconds or lower. Learn how to do that here.