Creating a database and a tables. 

Bellow you will find scripts for creating data base.
As a reminder, below scripts used for NYSE DATA !

The file system of our database.

nyse_data.csv – Main data content file.
nyse_data_new.csv – Temporary data file.
nyse_error.txt – Error data file.
nyse_ticker.txt – The file contains all NYSE tickers.

Create table:

Schema of the ticker table. (nyse_ticker.txt)

CREATE table nyse_ticker_list(
  ,ticker   VARCHAR(5) NOT NULL
  ,name     VARCHAR(100));asda

Schema of the original table. (nyse_data)
Schema is the same for the table (nyse_data_new)

CREATE TABLE nyse_data(
   Date      DATE  NOT NULL
  ,Symbols   VARCHAR(10) NOT NULL
  ,Adj_Close NUMERIC(21, 15) NOT NULL
  ,Close     NUMERIC(21, 15) NOT NULL
  ,High      NUMERIC(21, 15) NOT NULL
  ,Low       NUMERIC(21, 15) NOT NULL
  ,Open      NUMERIC(21, 15) NOT NULL
  ,Volume    NUMERIC(13, 2) NOT NULL);

Insert into:

Editing data in main table (nyse_data and nyse_ticker_list)
Note, inserting data in nyse_data can be a lit it bet longer then you expected!
More then 11 million lines in there.

You can delete file nyse_data.csv from your hard disk after inserting!

copy nyse_data FROM 'C:\file_path\nyse_data.csv' 
DELIMITER ',' CSV HEADER;
copy nyse_ticker_list FROM 'C:\file_path\nyse_ticker.txt' 
DELIMITER ',' CSV HEADER;

Checking data:

Simple and fast check. Should be more then 11 million.

select count(*) from nyse_data;

Other:

How to check duplicates, NULL, wrong data and how to COPY, DELETE and INSERT lines you can find in GitHub repository.
It`s to heavy for inserting on web page.

Done!

Leave a Reply

Your email address will not be published. Required fields are marked *