How to Query for YOY Quarterly Data in Analysis window?


I have daily EOD data for say 2 years. But I have also loaded Quarterly Result data for two tickers in SYNTHETIC Tickers ( Ticker_sn format).

The yellow row is the actual data while blue is which native AB DB it's mapped to.

What I want:

  1. I want to Query the Ticker where the Sales and Profit both are more in two consecutive Quaretrs compared to 1 year ago but the same Quarters. So as you can see the Red Circled Ticker and Quarter Combination corresponds to the Black Circled Ticker and Quarets as 1 Year Ago but Same Quarter. What we usually call "YoY"/ Year on Year Comparison.

  2. The Analysis Window output should be two rows ( using this data).

  1. When I click on a Ticker, I want to open the chart for MSFT. But in the output the ticker will be "MSFT_sn". So is this where the setforeign() or foreign() comes handy or any other way to solve it?

I am not able to figure out what should be my starting point to solve this?


Date	Ticker	Open	High	Close
2016-01-01	MSFT_sn	100	10	201601
2016-04-01	MSFT_sn	120	7	201602
2016-07-01	MSFT_sn	200	12	201603
2016-10-01	MSFT_sn	190	10	201604
2016-12-01	MSFT_sn	120	15	201701
2016-03-01	MSFT_sn	110	12	201702
2016-01-01	AAPL_sn	120	10	201601
2016-04-01	AAPL_sn	125	7	201602
2016-07-01	AAPL_sn	150	12	201603
2016-10-01	AAPL_sn	110	10	201604
2016-12-01	AAPL_sn	130	15	201701
2016-03-01	AAPL_sn	110	20	201702

Hi Neil,

You have broken down your question into several parts…

I can attempt to help you with the Logic in determining the conditions for Sales and Profits being met…
Assuming that you have data for all 4 quarters, you should be able to just use the Ref(array, period) function.
I don’t know why you would need the results on two lines, but think you can do it in one…
Here is some UNTESTED code to hopefully get you started in your analysis:

CondSales = Open > Ref(Open, -4); // Test if Sales is Greater than Last Year in same quarter
CondProfit = High > Ref(High, -4);  // Test Profit

Filter = CondSales and CondProfit;  // Set the Filter for the Analysis
AddColumn(O, "Sales");
AddColunn(H, "Profit");
AddColumn(C, "Qtr");

// Not sure if you can do this next part
AddColumn(Ref(Open, -4), "Prev Yr Sales");
AddColumn(Ref(High, -4), "Prev Yr Profit");
AddColumn(Ref(Close, -4), "Prev Yr Qtr");

As to Wanting to Chart one and Analyze another, why bother?
You just run this query once a quarter, so make up a Watch List of these synthetics and Let the Analysis results tell you when you need to dig deeper into the actual ticker.

Anyway, I hope this helps.