Category Archives: programming

How to calculate change and percent change over a time interval in mysql

I have a mysql application that I use to simulate various types of trading and trading strategies.  One of the tables generated is a table with the value of the simulated portfolio and the date with an entry for each day.  I wanted to use a mysql query to generate monthly, quarterly, and annual returns based on the account history table.  While this sounds fairly straightforward, it did become more involved that I thought, as I had to use variables in mysql queries, as well as date aggregration.  In addition, I needed to have the data pulled from the last day of the period instead of the first.

Here’s that I was able to come up with for annual data.  The account history table has two fields that are used:  Date, and Total_Equity with an entry for each day.

SELECT b.`yyyy`, CONCAT('$', FORMAT(b.Open_Equity,2)) as Open_Equity, 
	concat('$',FORMAT(delta, 2)) as delta,
    concat(round(delta_p*100,2),'%') as delta_p 
FROM   (SELECT *,
          (Open_Equity - @pequity) as delta,
          (Open_Equity - @pequity)/@pequity as delta_p,
          (@pequity:= Open_Equity)
        FROM (SELECT (EXTRACT(YEAR FROM `Date`)) as `yyyy`, 
			(SUBSTRING_INDEX(GROUP_CONCAT(CAST(`Open_Equity` AS CHAR) ORDER BY `Date` DESC), ',', 1 )) AS `Open_Equity`
			FROM account_history GROUP BY `yyyy` ORDER BY `yyyy` DESC) p
			CROSS JOIN
			(SELECT @pequity:= NULL) as a
       ORDER BY `yyyy` ) as b
ORDER by `yyyy` ASC

I’m not certain how ofter this sort of thing might come up, but I hope it will save someone some time and help with coding in mysql.

 

Here is some sample output:

yyyy   	Open_Equity	delta	        delta_p
2000	$79,794.00	NULL	        NULL
2001	$92,361.50	$12,567.50	15.75%
2002	$127,137.12	$34,775.62	37.65%
2003	$168,950.25	$41,813.13	32.89%
2004	$177,490.75	$8,540.50	5.06%
2005	$184,199.50	$6,708.75	3.78%
2006	$223,008.17	$38,808.67	21.07%
2007	$289,139.60	$66,131.43	29.65%
2008	$296,956.64	$7,817.04	2.70%
2009	$387,388.70	$90,432.06	30.45%
2010	$552,576.95	$165,188.25	42.64%
2011	$671,374.49	$118,797.54	21.50%
2012	$614,951.48	$-56,423.01	-8.40%
2013	$671,146.02	$56,194.54	9.14%