Package for XIRR
PL/SQLでやるとしても、どうせなら実際に使えるようにファンクションにした方がいいよな、ということで手を加えてみる。TBLCFというキャッシュフローデータが以下のレイアウトであるとして、CUSTID VARCHAR(10), CFDATE DATE, CFAMT NUMBER(36,0)このTBLCFにあるCUSTIDを指定するか、もしくはTBLCFを使わず、日付と金額の文字列をそれぞれカンマ区切りで渡すという2通りの方法でXIRRを返す。これならば、あらかじめ必要なキャッシュフローテーブルを用意しておけば、バルクで更新処理もかけられるし、コレクションと違って、アプリケーションからも呼べるだろう(とりあえず使う予定はないが)。考えてみたら、OracleにはIS_DATE, IS_NUMBERはないんだっけ。。ということで作る。EXCEPTIONを起こしてチェックするというやり方はあまり美しいとは思わないんだけど、他に手はあるのかな。。パラメータのうち、日付・金額と判断できるものだけで計算することにして、できたPackage Bodyはこんな感じ。何とか形になったような気がするから、この辺でよしにしよう。(FN_VARTOTBL)CREATE OR REPLACE PACKAGE BODY PKG_XIRR AS FUNCTION XIRR(P_DATESTR VARCHAR2,P_AMTSTR VARCHAR2) RETURN NUMBER AS W_CFDATE DATE; W_CFSUM NUMBER; W_XIRR NUMBER :=0; W_DELTA NUMBER; BEGIN SELECT MIN(TO_DATE(COLUMN_VALUE)) INTO W_CFDATE FROM TABLE(FN_VARTOTBL(P_DATESTR)) WHERE IS_DATE(COLUMN_VALUE)=1; SELECT SUM(TO_NUMBER(COLUMN_VALUE)) INTO W_CFSUM FROM TABLE(FN_VARTOTBL(P_AMTSTR)) WHERE IS_NUMBER(COLUMN_VALUE)=1; IF (W_CFSUM>0) THEN W_DELTA:=1000; FOR i IN 1..12 LOOP SELECT MAX(XIRR) INTO W_XIRR FROM (SELECT p.XIRR FROM (SELECT d.CFDATE,SUM(a.CFAMT) CFAMT FROM (SELECT ROWNUM SEQ,TO_DATE(COLUMN_VALUE) CFDATE FROM TABLE(FN_VARTOTBL(P_DATESTR)) WHERE IS_DATE(COLUMN_VALUE)=1 ORDER BY 2) d INNER JOIN (SELECT ROWNUM SEQ,TO_NUMBER(COLUMN_VALUE) CFAMT FROM TABLE(FN_VARTOTBL(P_AMTSTR)) WHERE IS_NUMBER(COLUMN_VALUE)=1 ORDER BY 2) a ON a.SEQ=d.SEQ GROUP BY d.CFDATE ) c CROSS JOIN (SELECT W_XIRR+TO_NUMBER(COLUMN_VALUE)*W_DELTA AS XIRR FROM TABLE(FN_VARTOTBL('0,1,2,3,4,5,6,7,8,9,10'))) p GROUP BY p.XIRR HAVING SUM(c.CFAMT/POWER((1+p.XIRR),(c.CFDATE-W_CFDATE)/365))>0); W_DELTA := W_DELTA / 10; END LOOP; ELSIF (W_CFSUM<0) THEN W_DELTA:=0.1; FOR i IN 1..9 LOOP SELECT MIN(XIRR) INTO W_XIRR FROM (SELECT p.XIRR FROM (SELECT d.CFDATE,SUM(a.CFAMT) CFAMT FROM (SELECT ROWNUM SEQ,TO_DATE(COLUMN_VALUE) CFDATE FROM TABLE(FN_VARTOTBL(P_DATESTR)) WHERE IS_DATE(COLUMN_VALUE)=1 ORDER BY 2) d INNER JOIN (SELECT ROWNUM SEQ,TO_NUMBER(COLUMN_VALUE) CFAMT FROM TABLE(FN_VARTOTBL(P_AMTSTR)) WHERE IS_NUMBER(COLUMN_VALUE)=1 ORDER BY 2) a ON a.SEQ=d.SEQ GROUP BY d.CFDATE ) c CROSS JOIN (SELECT W_XIRR+TO_NUMBER(COLUMN_VALUE)*W_DELTA AS XIRR FROM TABLE(FN_VARTOTBL('0,-1,-2,-3,-4,-5,-6,-7,-8,-9'))) p GROUP BY p.XIRR HAVING SUM(c.CFAMT/POWER((1+p.XIRR),(c.CFDATE-W_CFDATE)/365))<0); W_DELTA := W_DELTA / 10; END LOOP; END IF; RETURN W_XIRR; END XIRR; FUNCTION XIRR (P_CUSTID VARCHAR2) RETURN NUMBER AS W_DATESTR VARCHAR2(32767); W_AMTSTR VARCHAR2(32767); CURSOR C_CUR IS SELECT CFDATE, CFAMT FROM TBLCF WHERE CUSTID=P_CUSTID; BEGIN FOR r IN C_CUR LOOP IF W_DATESTR IS NULL THEN W_DATESTR:=TO_CHAR(r.CFDATE,'yyyy/MM/dd'); ELSE W_DATESTR:=W_DATESTR || ',' || TO_CHAR(r.CFDATE,'yyyy/MM/dd'); END IF; END LOOP; FOR r IN C_CUR LOOP IF W_AMTSTR IS NULL THEN W_AMTSTR:=TO_CHAR(r.CFAMT); ELSE W_AMTSTR:=W_AMTSTR || ',' || TO_CHAR(r.CFAMT); END IF; END LOOP; RETURN XIRR(W_DATESTR,W_AMTSTR); END XIRR; END PKG_XIRR;