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;