I've been asked to re-create a spreadsheet that used RBloomberg using a different data source. But I'm having trouble figuring out exactly what one of the spreadsheet's formulas does. Can anyone tell me what exactly the following formula is calculating?

I think it's something along the lines of (CurrentPrice/30dayEMA-1)*100. But I can't seem to get that calculation to match the values given by this formula. I wonder if this is caused by differences in things like whether this formula is using adjusted prices or not, when the EMA calculation starts, etc.


=IF($A19<>"",(BDH($A19,"PX_LAST",$B$7,$B$7,"Days=A", "Fill=P", "Dts=H")/BTH($A19,"EMAVG",$B$7,$B$7,"EMAVG","TAPeriod=30","DSClose=PX_LAST","Dir=V","Dts=H","Sort=A","QtTyp=P","Days=T","Per=cd","UseDPDF=N", "CshAdjNormal=Y","CshAdjAbnormal=Y","CapChg=Y")-1)*100,"")

(A19 is a security name.B7 is the current date.)

  • $\begingroup$ Yes it looks like it - have you taken the overrides into account? ("CshAdjNormal=Y","CshAdjAbnormal=Y","CapChg=Y") $\endgroup$ – assylias Jul 5 '15 at 21:03
  • $\begingroup$ What do the overrides do? $\endgroup$ – ransomedbyfire Jul 6 '15 at 8:44
  • $\begingroup$ They affect the way corporate actions are taken into account (splits, dividends etc.). $\endgroup$ – assylias Jul 6 '15 at 11:42
  • $\begingroup$ I've tried using adjusted prices (like Yahoo's) and unadjusted prices. I believe this should account for dividends, splits, and the like. Neither option seems to work. $\endgroup$ – ransomedbyfire Jul 26 '15 at 6:42
  • $\begingroup$ You should ask HELP HELP on your terminal. $\endgroup$ – assylias Jul 26 '15 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.