Calculating and updating table with simple moving average of closing stock prices in MYSQL

Question

I could use some help (preferably a dummy's guide) to updating the following table:

CREATE TABLE `SYMBOL` (
  `day` date NOT NULL,
  `open` decimal(8,3) DEFAULT NULL,
  `high` decimal(8,3) DEFAULT NULL,
  `low` decimal(8,3) DEFAULT NULL,
  `close` decimal(8,3) DEFAULT NULL,
  `volume` bigint(20) DEFAULT NULL,
  `adj_close` decimal(8,3) DEFAULT NULL,
  `moving_average` decimal(8,3) DEFAULT NULL,
  PRIMARY KEY (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The moving_average column is empty now. All other columns are populated (for the time being, I'm ok with this being "static", it does not need to update as I add rows - though if this is easy to do, that would be great). It is a 20-day moving average that I hope to calculate.

I have tried by performing the steps here to the best of my ability:

How do I calculate a moving average using MySQL?

My query is this:

SELECT
     `close`,
     (
     SELECT
          AVG(`close`) AS moving_average
     FROM
          SYMBOL T2
     WHERE
          (
               SELECT
                    COUNT(*)
               FROM
                    SYMBOL T3
               WHERE
                    `day` BETWEEN T2.day AND T1.day
          ) BETWEEN 1 AND 20
     )
FROM
     SYMBOL T1

Have I modified the query correctly? What needs to be done to write the results to the moving_average column?

When I run the above, nothing happens (it says its running, no errors, after letting it run for a long time I just stopped it). The column moving_average still has NULL values.

I also looked at this answer: How to calculated multiple moving average in MySQL

However, I'm unsure what I need to change to the reply for my table.

Any help is appreciated.

The statement you posted has no update statement in it. Just a select. Naturally, this means nothing will get updated. — SchmitzIT, Oct 21 '12 at 13:22

Barranka · Accepted Answer · 2012-10-22T05:03:06.577

2

There are two ways of doing this:

Create an update query that updates every row in your table
Create a stored procedure that does the job

I personally prefer option 2:

delimiter $$
create procedure movingAvg()
begin
    declare mv double;
    declare t date;
    declare done int default false;
    declare cur_t cursor for
         select distinct day from symbol
         order by day;
    declare cur_mv cursor for
         select avg(close) from symbol
         where day between date_add(t, interval -19 day) and t;
         -- Here you define the interval of your MV.
         -- If you want a 20-day MV, then the interval is between t-19 and t
    declare continue handler for not found set done=true;

    open cur_t;
    loop_day: loop
        fetch cur_t into t;
        if not done then
            open cur_mv;
            fetch cur_mv into mv;
            close cur_mv;
            update SYMBOL
                set moving_average = mv
                where day=t;
        else
            leave loop_day;
        end if;
    end loop loop_day;
    close cur_t;
end;
delimiter ;

edited Oct 22 '12 at 05:03

answered Oct 21 '12 at 13:56

Barranka

19,769
13
62
81

Thank Barranka - I copied and pasted this and attempted to run it. The result was "Query was empty". I also did "show procedure status" and it was empty. Should I modify this further? Either way, thank you for the help! – majordomo Oct 21 '12 at 14:20
@user1644609 you may need to check that i wrote everything right (i think i didn't screwed up, but there's always the chance i missed something), and then check your table... are the calculations correct? – Barranka Oct 21 '12 at 14:24
I've tried to "debug" it, but honestly don't know whether its correct or not. I've played around with it a little bit (and will continue to) but haven't been able to create the stored procedure, nor populate date into the moving_average column. But thank you for putting me on a track. – majordomo Oct 21 '12 at 14:35
Thanks a lot Barranka, I got it working after adding a semicolon at the end of the cur_mv declaration and changing the delimiter. Really appreciate the help. Can you recommend any online reading as I will be doing more calculations like this (more complicated) in the future? Again, thanks a lot! – majordomo Oct 21 '12 at 19:41
@user1644609 Unfortunately I don't know about a site (other than this very site) where you can find help... The only thing I know for sure is that there's no better teacher than your own curiosity. Keep a copy of MySQL reference manual at hand, and take a look at it from time to time if you feel lost. Other than that, be curious and creative – Barranka Oct 22 '12 at 05:01
@user1644609 by the way, thank you for finding the error (sorry about it, simply missed it); I've added the missing semicolon – Barranka Oct 22 '12 at 05:03
Is there a way to change the table reference (SYMBOL) to a variable in the procedure, so that when calling the procedure the variable can be called? ex. call movingAvg() Symbol; ? – majordomo Oct 22 '12 at 14:25

BellevueBob · Answer 2 · 2012-10-21T22:08:49.667

Here is a possible solution:

update SYMBOLS
from (
   select a.day
        , avg(b.close) as moving_average
   from   SYMBOLS a
   cross join SYMBOLS b
   where b.day BETWEEN date_sub(a.day, INTERVAL 19 DAY) and a.day
      and a.moving_average is null
   group by a.day
   ) x
set moving_average=x.moving_average
where SYMBOLS.day=x.day

Sorry I don't use mysql myself, so I'm guessing on the date arithmetic syntax. And I added a condition to only update rows where your moving_average is null.

UPDATE: Be sure you understand that this solution is based on a 365-day calendar. Most stock market averages like "20-day" or "30-day" are based on a trading calendar which excludes weekends and holidays. You would need to create a trading calendar table youself (a simple list of all trade dates). If you want to do that, you might want to ask a new question to that effect.

Calculating and updating table with simple moving average of closing stock prices in MYSQL

2 Answers2

Linked