Check for price changes over multiple periods

[PS01]

Hi,

I have a table containing amongst others the following attributes:
Period (yyyymm)
ContractID
Price

The table contains approximately 3 years of historical information (36 periods). I am trying to write a query that presents the contract details of contracts having a price change of more than XX% ((1-XX)<PriceT/PriceT-1 > (1+XX)) over the last XY consecutive periods. I have tried all kind of subqueries but can't get my head around this.

Can anybody help me please?

Kind regards,

Paul

 

[plog]

XX% ((1-XX)<PriceT/PriceT-1 > (1+XX))

You lost me with the above formula. What is XX? what field is PriceT? Can you provide some sample data from your table (along with its name) and then what data should show up in your query based on that sample data?

 

[PS01]

Thanks for your response. The parameter XX in the formula represents a percentage, the Parameter PriceT represents the price of a security in period T, while the parameter PriceT-1 represents the price of the same security in the previous period.

Please find below a simplified example. The PriceTable contains three attributes: a period, a product id and the price of the product in that period. I am looking for a query that provides me per the last period (in this case 201210) all securities having a price change of more than plus or minus XX% (in this case 3%) in the last XY (in this case 3) months. The three columns on the right hand provide some calculations to further clarify this:

• Delta is the price change from one period to the other (note that I changed the formula slightly to (PT-PT-1)/PT-1
• Delta>Threshold: checks whether the change is larger than (plus or minus) 3% (parameter XX)
• Counter: checks whether the price change is larger than 3% for 3 (parameter XY) consecutive months

In the example below the query should only show productID number 1.

PriceTable Supporting calculations
Period ProductID Price Delta% Delta>Threshold Counter
201206 1 105 0% N 0
201207 1 100 -4.76% Y 1
201208 1 95 -5% Y 2
201209 1 90 -5.26% Y 3
201210 1 85 -5.56% Y 4
201207 2 95 0% N 0
201208 2 100 5.26% Y 1
201209 2 103 3% N 0
201210 2 99 -3.88% Y 1


I really hope you can help me with the query!

 

[plog]

You're not helping me, help you, when you provide unsolicited help. Here's what I want from you:

Sample data from the table, no more no less. Then based on that sample data the data you expect your query to return based on that sample data.

Don't do any intermediate calculations that you think are helpful. Don't iterate your forumlas. Don't invent new fields that I am unable to determine if they are in the table or the query. Sample data and what you expect as the end result. No more, no less.

 

[PS01]

Please find below sample data from the table

Period SecID Price
201206 1 105,00
201207 1 100,00
201208 1 95,00
201209 1 90,00
201210 1 85,00
201207 2 95,00
201208 2 100,00
201209 2 103,00
201210 2 99,00

The evaluation period is 201210 and the threshold is 3%. The query should return SecID 2.

Thanks again for your help!

 

[plog]

Why wouldn't SecID 1 be returned from the sample data you posted? Every record represents a absolute change of more than 3% from the record prior to it.

Actually, in your second post you stated that secID 1 should be returned. It looks like the same data as in your 3rd post, but you stated only secID 2 should be returned. What am I missing?

 

[PS01]

My apologies, you are not missing anything, I was sleeping... The data is indeed the same as in my previous post and the query should of course return SecID 1!

 

[plog]

More data, this time provide records that fail to make it into the final output.