Author Archives: proprietor

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%

Ethanol in Gasoline: Not What You Expect

After some reading about the dangers of ethanol to marine engines in Seaworthy, I decided to see what difference it made in my passenger automobile.  I heard that there were several gas stations in my town that had ethanol free gasoline for sale, and most of them were about the same price as regular fuel.  I had been warned by some kindly folks to avoid fuel containing ethanol for marine usage, especially on older engines as it would cause a host of problems.

I had also heard that ethanol contained less energy than ethanol-free gasoline.  I decided that I would find some ethanol-free gas stations and try a couple of tanks through my 1999 Chevy Malibu to see if I could determine a difference.

I had been getting about 265 miles per tank of gas using the gas I purchased from a station close to my house that had a sticker on the pump stating that “this fuel may contain up to 10% ethanol by volume.”  Therefore, I don’t know exactly how much (if any) ethanol was in any given tank I purchased there, but I did have a track record of my normal driving habits for several years where I was able to get 265 miles per fill-up.  Also, my check engine light was illuminated, and my local mechanic informed me that the computer indicated an oxygen sensor was the culprit.  Since I wasn’t in violation of any emissions laws in my state, I elected to not have this defect repaired.

Where can you find ethanol free gasoline?  One way is to look for signs, as many gas stations will find that selling gas sans ethanol gives them a competitive advantage.  I asked my neighbor who knows everyone and everything about everyone, and he gave me a couple of ideas.  Perhaps the easiest way that I found was to go to www.pure-gas.org and search by your state. Several that my neighbor told me sold ethanol free gasoline weren’t listed on this site, so I called and asked them to be sure.

Did using ethanol-free gas make a difference in my case? Using the same driving habits, (no long road trips or unusual amount of highway driving) I’ve been able to get more than 300 miles per tank of fuel. With gas prices as high as they’ve been lately, the extra 35 miles is probably worth about $6 per fill-up. In addition, my check engine light now is off most of the time. I’m guessing that in 1999, my automobile wasn’t manufactured with ethanol fuel in mind, so when the fuel was changed, the car wasn’t able to adjust to the less-powerful ethanol diluted fuel. I suspect that newer vehicles may not have such a noticeable difference.

One small nugget of good news: the government has ended its $.45 a gallon subsidy for ethanol. Does it surprise anyone that the government would subsidize something that would make our vehicles less efficient?