Difference between revisions of "Programming performance/ScottN MySQL"
Jump to navigation
Jump to search
(programming attempt using MySQL) |
m (tabs to spaces) |
||
Line 8: | Line 8: | ||
DROP TABLE IF EXISTS stockData; |
DROP TABLE IF EXISTS stockData; |
||
CREATE TABLE 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) |
|
); |
); |
||
Line 23: | Line 23: | ||
DELIMITER // |
DELIMITER // |
||
CREATE PROCEDURE simulation(INOUT cash FLOAT) BEGIN |
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// |
END// |
||
Line 75: | Line 75: | ||
CALL simulation(@cash); |
CALL simulation(@cash); |
||
SELECT @cash; |
SELECT @cash; |
||
+ | |||
</code> |
</code> |
Latest revision as of 15:05, 9 March 2007
- 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;