bnsheehy

bnsheehy

# Import credentials

import json
f = open("/Users/......./credentials.json")
credentials = json.load(f)

file_path = list(credentials.values())[0]
intrinio_key = list(credentials.values())[1]
aws_key = list(credentials.values())[2]
aws_secret_key = list(credentials.values())[3]

bnsheehy

CREATE TABLE IF NOT EXISTS `price_data_historical` (
`key_id` varchar(30) NOT NULL PRIMARY KEY,
`ticker` varchar(8) NOT NULL,
`figi` varchar(14) NOT NULL,
`date` datetime NOT NULL,
`open` float NULL,
`high` float NULL,
`low` float NULL,
`close` float NULL,
`volume` float NULL,

bnsheehy

LOAD DATA LOCAL INFILE '/Users/....../data_files/df_full_price_history_complete_load.csv' 
INTO TABLE price_data_historical 
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
IGNORE 1 LINES;

bnsheehy

CREATE INDEX idx_ticker ON price_data_historical (ticker(8));

CREATE INDEX idx_figi ON price_data_historical (figi(14));

CREATE INDEX idx_date ON price_data_historical (date);

CREATE INDEX idx_update_date ON price_data_historical (last_update_date);

CREATE INDEX idx_corp_action_date ON price_data_historical (last_corp_action_date);

bnsheehy

CREATE TABLE price_data_historical_bkup AS SELECT * FROM price_data_historical;

INSERT INTO price_data_historical_bkup AS SELECT * FROM price_data_historical;

bnsheehy

CREATE TABLE IF NOT EXISTS `security_info` (
`key_id` VARCHAR(25) NOT NULL,
`ticker` VARCHAR(8) NOT NULL,
`figi` VARCHAR(14) NOT NULL,
`company_name` VARCHAR(100) NOT NULL,
`company_id` VARCHAR(12) NOT NULL,
`security_id` VARCHAR(12) NULL,
`composite_ticker` VARCHAR(14) NULL,
`share_class_figi` VARCHAR(14) NULL,
`composite_figi` VARCHAR(14) NULL,

bnsheehy

{"file_path": <my file path>,
"intrinio_key": <my Intrinio API key>,
"aws_access_key": <my AWS access key>,
"aws_secret_key": <my AWS secret key>,
"rds_host": <my RDS host URL>,
"rds_user": <my RDS user name>,
"rds_password": <my RDS password>,
"rds_database": <my RDS database name>,
"rds_charset": <my RDS characterset code>
}

bnsheehy

CREATE TABLE IF NOT EXISTS `base_pnf_data_historical` (
    `key_id` varchar(40) NOT NULL,
    `date` datetime NOT NULL,
    `figi` varchar(14) NOT NULL,
    `ticker` varchar(8) NOT NULL,
    `open` float NULL,
    `high` float NULL,
    `low` float NULL,
    `close` float NULL,
    `change` float NULL,

bnsheehy

LOAD DATA LOCAL INFILE '/Users/....../data_files/df_pnf_data_history_complete_load.csv' 
INTO TABLE price_data_historical 
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
IGNORE 1 LINES;