Programming performance/ScottN MySQL

• Language: MySQL
• Skill: Advanced (MySQL in general), Intermediate (Stored Procedures in MySQL)
• Time: 23 minutes.
• Notes: I spent more time trying to understand the problem than coding the solution.

Code

DROP TABLE IF EXISTS stockData;
CREATE TABLE stockData (
    date DATE, 
    open DECIMAL(8, 2), 
    high DECIMAL(8, 2), 
    low DECIMAL(8, 2), 
    close DECIMAL (8, 2), 
    volume INT UNSIGNED, 
    adjClose DECIMAL(8, 2)
);

LOAD DATA INFILE 'gspc.txt' INTO TABLE stockData FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' IGNORE 1 LINES;

DROP PROCEDURE IF EXISTS simulation;

DELIMITER //
CREATE PROCEDURE simulation(INOUT cash FLOAT) BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE holdingsDone INT DEFAULT 0;
    DECLARE newClose DECIMAL(8,2) DEFAULT 0;
    DECLARE oldClose DECIMAL(8,2) DEFAULT 0;

    DECLARE stockCursor CURSOR FOR SELECT close FROM stockData ORDER BY date ASC;

    DECLARE holdingCursor CURSOR FOR 
        SELECT newClose * shares + cash 
        FROM holdings 
        WHERE (newClose - sharePrice) / sharePrice >= 0.06;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    DROP TABLE IF EXISTS holdings;
    CREATE TEMPORARY TABLE holdings (sharePrice FLOAT, shares FLOAT);

    OPEN stockCursor;

    REPEAT
        FETCH stockCursor INTO newClose;
        IF NOT done THEN

            -- Buy if the price is down
            IF oldClose < 0.01 OR (newClose - oldClose) / oldClose < -0.03 THEN
                INSERT INTO holdings VALUES (newClose, (cash * .1) / newClose);
                SET cash := cash * .9;
            END IF;

            -- Sell if the price is up
            BEGIN
                DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET holdingsDone = 1;
                    OPEN holdingCursor;
                    REPEAT
                        FETCH holdingCursor INTO cash;
                    UNTIL holdingsDone END REPEAT;
                    CLOSE holdingCursor;

                    DELETE FROM holdings WHERE (newClose - sharePrice) / sharePrice >= 0.06;
            END;
            SET oldClose := newClose;
        END IF;
    UNTIL done END REPEAT;

    -- Sell remaining stock
    SELECT newClose * shares + cash INTO cash FROM holdings;
END//

DELIMITER ;
SET @cash := 10000;
CALL simulation(@cash);
SELECT @cash;