外汇掉期估值计算

由于每一个合约有不同的起息日和合约期限，因此会对应不同的互换利率以及全价汇率。普通的实现方法是自定义一个包含估值计算逻辑的函数，通过循环语句，将每个合约与对应利率进行计算。

包含三表的估值函数定义如下：

def valuationDtmRate(today, swap_rate, IR_swap, maturity_date,far_leg_spot_rate, near_leg_amount){
	target_swap_rate_next = select * from swap_rate where Date=today, currencyPair = `USD.CNY, maturityDate >=maturity_date limit 1
	target_swap_rate_prev = select * from swap_rate where Date=today, currencyPair = `USD.CNY, maturityDate <=maturity_date order by maturityDate desc limit 1
	tartget_IR_swap = select * from IR_swap where Maturity in (target_swap_rate_prev.termToMaturity[0],target_swap_rate_next.termToMaturity[0])
	fex = target_swap_rate_prev.exchangeRate +(maturity_date - target_swap_rate_prev.maturityDate)\ (target_swap_rate_next.maturityDate-target_swap_rate_prev.maturityDate) * (target_swap_rate_next.exchangeRate - target_swap_rate_prev.exchangeRate)
	sir = tartget_IR_swap.selling_rate[0] + (maturity_date - target_swap_rate_prev.maturityDate)\ (target_swap_rate_next.maturityDate-target_swap_rate_prev.maturityDate) * ( tartget_IR_swap.selling_rate[1] -  tartget_IR_swap.selling_rate[0])
	dicount_factor = exp(-sir \ 100 * (maturity_date-today)\365)
	valuation = (fex - far_leg_spot_rate) * dicount_factor * near_leg_amount
	return valuation
}

由于涉及到多表间的信息提取，函数看起来会相对复杂。做完这一步之后，就可以对所有的合约进行循环计算。100万个合约在测试环境下做计算耗时约3分20秒。

for循环写法的代码示例如下：

today = 2021.12.14
k = array(DOUBLE, 0)
for (x in fx_contract){
	k.append!(valuationDtmRate(today, swap_rate, IR_swap,x.maturity_date, x.far_leg_spot_rate, x.near_leg_amount))
}
result = fx_contract join table(k as valuation)

或用loop函数的写法示例如下：

k = loop(valuationDtmRate{today, swap_rate, IR_swap},fx_contract.maturity_date, fx_contract.far_leg_spot_rate, fx_contract.near_leg_amount).flatten()
result = fx_contract join table(k as valuation)

利用多线程并行计算，可以在同样的计算逻辑上做进一步优化，将原本的单线程循环改成多线程并行计算，最后将结果进行拼接。DolphinDB 提供了 ploop/peach 函数，可以很方便的把这类循环语句内存计算转换成多线程任务。

此例用了8个并行度做计算，最后耗时大约20秒：

k = ploop(valuationDtmRate{today, swap_rate, IR_swap},fx_contract.maturity_date, fx_contract.far_leg_spot_rate, fx_contract.near_leg_amount).flatten()
result = fx_contract join table(k as valuation)

注意：在批流一体的因子实现中，不建议使用 ploop/peach 并行加速。具体并行策略可以参考因子最佳实践中的并行计算章节。

DolphinDB 中提供了许多表关联函数，故原本的两张小表 swap_rate 和 IR_rate 以及合约大表，可以先通过表关联函数将有效信息提取出来，继而直接通过 SQL 做计算即可。如此一来，避免了循环语句重复消耗资源，也减少了代码的开发量。

做外汇掉期估值计算时，可以用 equal join , asof join 及 window join 将需要的远端利率等信息通过三表关联得到，代码如下：

today = 2021.12.14
tmp = select * from ej(swap_rate,IR_swap,`termToMaturity, `Maturity) where Date = today
res_aj=aj(fx_contract,tmp, `maturity_date, `maturityDate)
res_wj=wj(res_aj,tmp, 0d:366d,<[first(termToMaturity) as next_termToMaturity, first(exchangeRate) as next_exchangeRate, first(maturityDate) as next_maturityDate,first(buying_rate) as next_buying_rate, first(average_rate) as next_average_rate, first(selling_rate) as next_selling_rate]>, `maturity_date, `maturityDate)

由于关联后的信息可以在一张表( res_wj )中得到，故计算逻辑也可以得到简化，可以直观地翻译原公式逻辑进行向量化计算：

def valuationSQL(exchangeRate,maturity_date, maturityDate, next_maturityDate, next_exchangeRate, far_leg_spot_rate, selling_rate, next_selling_rate,today, near_leg_amount){
	return (exchangeRate + (maturity_date - maturityDate)\ (next_maturityDate-maturityDate) * (next_exchangeRate - exchangeRate) - far_leg_spot_rate) * exp(-(selling_rate +  (maturity_date - maturityDate)\ (next_maturityDate-maturityDate) * (next_selling_rate - selling_rate))\100 * (maturity_date-today)\365) * near_leg_amount
}

用优化后的表关联逻辑做外汇掉期估值计算，整体耗时只需0.5秒左右，性能相比最初提升了超400倍。

timer{
	today = 2021.12.14
	tmp = select * from ej(swap_rate,IR_swap,`termToMaturity, `Maturity) where Date = today
	res_aj=aj(fx_contract,tmp, `maturity_date, `maturityDate)
	res_wj=wj(res_aj,tmp, 0d:366d,<[first(termToMaturity) as next_termToMaturity, first(exchangeRate) as next_exchangeRate, first(maturityDate) as next_maturityDate,first(buying_rate) as next_buying_rate, first(average_rate) as next_average_rate, first(selling_rate) as next_selling_rate]>, `maturity_date, `maturityDate)
	
	result = select contract_no,contract_date,trade_date,maturity_date,buy_sell, period, near_leg_currency, near_leg_amount, near_leg_spot_rate, far_leg_currency, far_leg_spot_rate, valuationSQL(exchangeRate,maturity_date, maturityDate, next_maturityDate, next_exchangeRate, far_leg_spot_rate, selling_rate, next_selling_rate,today, near_leg_amount) as valuation from res_wj
}

对于需要用到多表数据计算的任务，用循环语句的方式性能较差。建议先用 DolphinDB 优化的 asof join 等表关联函数将需要的信息提取。asof join 等表关联函数亦支持分布式关联，对于存储于数据库中的分布式表也可以多线程并行关联，性能卓越。计算部分建议使用 SQL 向量化编程的方式做计算，不仅可以减少代码开发量（基本可以直观地翻译计算公式），也避免了循环语句重复消耗 CPU 资源。

• 数据准备脚本
• 外汇掉期估值的优化实现脚本
• 完整优化过程