Calculate Number of Up Days Using MySQL Stock Database

I want to show a simple technique using a MySQL stock database. This could potentially be used to give insight into how a stock is currently trending. We will calculate the percentage of "up" days over a given interval.

1. Choose your favorite symbol.
   
2. If you already have day info in your database, skip this step. If you have minute info, you'll need to extract each day's open and close data. Use something like the following (switch in your favorite symbol for MSFT) :
   
   CREATE TEMPORARY TABLE IF NOT EXISTS temp_open_close AS (
   SELECT DATE(s1.date) as date, s1.open as open, s2.close as close
   FROM stocks.stock_prices_minute s1
   INNER JOIN stocks.stock_prices_minute s2
   ON DATE(s1.date) = DATE(s2.date)
   AND s1.symbol = s2.symbol
   WHERE s1.date LIKE '%6:30:00'
   AND s2.date LIKE '%12:59:00'
   AND s1.symbol = 'MSFT')
   
3. Now that we have daily open and close info, let's calculate the day's up/down value. A "1" will represent an "up" day, and a "0" will represent an "unchanged"/"down" day. Use the following:
   
   CREATE TEMPORARY TABLE IF NOT EXISTS temp_values AS (
   SELECT date, CEIL(ln(close/open)) as upvalue
   FROM temp_open_close)
   
   - CEIL() is a function that rounds a value to the next highest integer. (Note: If you're dealing with stocks that increase >100% you will get more than "1" for those up days).
4. Now we want to find the total up days over a specified period of time.
   
   SELECT COUNT(date), SUM(upvalue)/COUNT(date)
   FROM temp_values
   WHERE date >= SUBDATE('2013-11-11', INTERVAL 40 DAY)
   
   

Conclusion:

This will print out the number of trading days along with the fraction that were up. (This example starts on 2013-11-11, but could be started from any point in history). In this case I get 

28	trading days with a 53.57% of those being "up".

If you're interested in seeing code that will populate a table with rolling values of this data, sign up for the free Gödel's Market Newsletter. I will be sending out code later this week.