- Choose your favorite symbol.
- 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') - 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). - 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
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.
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.
No comments:
Post a Comment