How can I do running sim of Quarterly Sales at Yearly level for each Quarter?

Hi,

Please refer to below excel screenshot:

My Pricing data is daily EOD level. My Sales data is in Synthetic Ticker "_st1". First row in image is mapping of data to amibroker fields. I am calling my synthetic ticker using foreign() without filling holes and replacing 0 where there is null.

Below is the Quarterly Sales data. On each quarter I want to sum (refer Cum_Sales) last four quarters sales.

So for Year 2017 and Quarter 2 the Cum_Sales 530 is a sum of:

yr 2017 qtr 2 = 150 +
yr 2017 qtr 1 = 190 +
yr 2016 qtr 4 = 100 +
yr 2016 qtr 2 = 90 = 530.

And likewise for previous quarters.

**If i do not align using foreign() then its very easy to do using ref(). **

But how to achieve this when I am using Foreign() and aligning the sales data with my daily EOD stock Price Data?

Any thoughts on this case?

This is out of the top of my head.
So it’s untested (also since I don’t have your data).

PS: I don’t know if the sym variable is correct so you have to change to actual name if it is different one.

/// @link http://forum.amibroker.com/t/how-can-i-do-running-sim-of-quarterly-sales-at-yearly-level-for-each-quarter/1603/3
sym = Name() + "_st1";// synthetic ticker name

quarter = Foreign( sym, "1" );// Foreign Aux1 field
sales = Foreign( sym, "2" );// Foreign Aux2 field

cond = quarter > 0;//if Aux1 > 0 

sparsecomp = SparseCompress( cond, sales );
sumsales = Sum( sparsecomp, 4 );// sum of last four quarters
sparseexp = SparseExpand( cond, sumsales );

Plot( Valuewhen( cond, sparseexp ), "Sum Sales of last 4 quarters", colorRed, styleHistogram, Null, Null, 0, 0, -60 ); 

So please report back whether it works.

3 Likes

Dear @fxshrat

Your solution is terrific and ditto what I needed.

Here is how it looks:

It has cumulative sales and Operating Profit Margin (next adjoining bar). Basically this chart showing on a QoQ basis the Operating margins are growing faster than the revenue.

THANK YOU VERY VERY MUCH FXSHRAT.

2 Likes

As an addition: I am trying to plottext on this chart to show Sales. But as you can see from below code, the sales bar is leftaxis scaled and plottext() Y position is rightaxis scaled, hence the Text position is dependent on the price.

I feel gfx is the only way to solve it. But well beyond my AFL capabilities.

for( i = 0; i < BarCount; i++ ) 
{ 
if( sales_exp[i] != 0 ) PlotText( "Sales " + round( sales_exp[i] ) + " Mn.", i, sales_exp[i], colorWhite ); 
} 

Plot( C, "Close", colorWhite, styleLine );
Plot( sales_exp, "Sales_s", sales_qtr_color, styleHistogram | styleThick | styleLeftAxisScale,Null,Null,-6,1, 5 );