FAQ > 金融建模 > 建模问题 > 其他

Q:如何实现多个指标按百分位排名进行打分    

  • A:可封装打分函数例如:getScorebyRank(t,FileName,r,s,sortdir)
    实现指定列按指定顺序排序后,按排序的百分位进行分组,进行打分。

    function getScorebyRank(t,FileName,r,s,sortdir);
    begin
    {t 数组
    FileName 字符串,打分的列
    r 百分位分组序列,前开后闭,例如array((0,20),(20,40),(40,60),(60,80),(80,100));
    s 打分序列 与r对应,例如array(5,4,3,2,1)
    sortdir为排序方向:1 正序,0 逆序
    }
      lent:=length(T);
      if sortdir then
       t1:= select *,thisorder as FileName$'-排名'
          from t order by [FileName] end;
      else
       t1:= select *,thisorder as FileName$'-排名'
          from t order by [FileName] desc end;
      t1:= select *,[FileName$'-排名']/lent*100 as '分组' from t1 end;
      for i:=0 to length(r)-1 do
       update t1 set [FileName$'-得分']=s[i]
        where ['分组']>r[i,0] and ['分组']<=r[i,1] end;
      reindex(t1,nil,array('分组':Nil));
      return t1;
    end;


    调用案例:实现对指标夏普,詹森、特雷诺、信息比、标准差按指定规则进行排名打分。
    其中,夏普,詹森、特雷诺、信息比按从大到小分成均匀五组,第一组得5分,第二组得4分,依此类推;
    标准差按从小到大分成不均匀五组,打分同上,最后加总得到总得分。

      t:= select ['StockID'],['截止日'],['夏普(%)'],['詹森(%)'],["特雷诺(%)"],
           ["信息比(%)"],['标准差(%)']
        from infotable 622 of getbk('ETF')
        where ['截止日']=20200731 and ['数据类型']='最近1年' end;
      //-百分位排名分组
      r1:=array((0,20),(20,40),(40,60),(60,80),(80,100)); //前开后闭
      //-分组对应分数
      s1:=array(5,4,3,2,1);
    //不同的指标,可按不同的排序方向,以及给不同的排名方式进行打分。给不同的r,s即可。
      t:= getScorebyRank(t,'夏普(%)',r1,s1,0);
      t:= getScorebyRank(t,'詹森(%)',r1,s1,0);
      t:= getScorebyRank(t,'特雷诺(%)',r1,s1,0);
      t:= getScorebyRank(t,'信息比(%)',r1,s1,0);
      
      r2:=array((0,10),(10,20),(20,30),(30,60),(60,100)); //前开后闭
      s2:=array(5,4,3,2,1);
      t:= getScorebyRank(t,'标准差(%)',r2,s2,1);
      t:= select *,
        ['夏普(%)-得分']+['詹森(%)-得分']+['特雷诺(%)-得分']
        +['信息比(%)-得分']+['标准差(%)-得分'] as '总得分'
        from t end;
      return select * from t order by ['总得分'] desc end;

    //返回结果:
    StockID截止日夏普(%)詹森(%)特雷诺(%)信息比(%)标准差(%)夏普(%)-排名夏普(%)-得分詹森(%)-排名詹森(%)-得分特雷诺(%)-排名特雷诺(%)-得分信息比(%)-排名信息比(%)-得分标准差(%)-排名标准差(%)-得分总得分
    OF5101502020073136.6667000.8302001.21720051.3518002.79540012528520524557323
    OF1599732020073132.6020000.7292000.98500056.2781002.92310021531535511586222
    OF5106602020073141.8080001.0188001.84140046.3537002.78690045856543456322
    OF5188802020073125.6698000.5333003.91650024.5434002.20810038549415103315522
    OF1599372020073125.6121000.5316003.89280024.4818002.20680039551445104314522
    OF1599342020073125.6896000.5330003.91300024.5622002.20530037550435102312522
    OF1599492020073137.1641001.1548001.17740057.5489003.8671001055522595176121
    OF1599482020073132.1668000.8947001.00660049.4509003.611300255215315335168121
    OF1599152020073132.2927000.8978001.01060049.6885003.605500225195285315166121
    OF1599522020073132.0071000.8827000.99900049.4783003.588800285255335325165121
    OF1599582020073132.2196000.8871001.00690049.7248003.574600235235295305164121
    OF1599082020073132.0552000.8801001.00320049.2153003.568600275265325355163121
    ......