Programming performance/ScottN MySQL
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
- 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;