Cómo crear una base de datos de acciones

por

Cuando investigue lo que funciona en los mercados, querrá almacenar sus datos en una base de datos. Hay muchas razones por las cuales, pero los dos propósitos principales son la persistencia y la flexibilidad de los datos. Le mostraré cómo crear su propia base de datos de acciones en PostgreSQL y cómo crear las tablas a través de una GUI, Python o SQL, según sus necesidades. Y aunque esta base de datos es para acciones, podría adaptarse fácilmente a diferentes mercados, como futuros o forexo diferente tecnología de base de datos como MySQL o MSSQL.

Diseño de una base de datos de acciones

Antes de diseñar su base de datos, es importante comprender el mercado con el que está trabajando. Por ejemplo, backtesting de una estrategia de renta variable frente al S&P500 nos obligaría a considerar una serie de cuestiones relacionadas con los datos:

  • ¿Cuál es nuestro deseado trading ¿frecuencia? ¿Queremos necesitar datos diarios o más frecuentes?
  • ¿Hay sesgo de supervivencia en nuestro conjunto de datos? ¿Qué pasa con las fusiones, las escisiones y los símbolos cambiados?
  • ¿Tenemos un retorno total? ¿Estamos agregando nuevamente dividendos, recompras y otras acciones corporativas a nuestros datos de precios?

    • Con estas consideraciones en mente, estamos listos para diseñar nuestra base de datos. Al desarrollar una base de datos compleja, es posible que desee utilizar un modelador de base de datos. Los modeladores de bases de datos, como SqlDBM, le permiten trazar visualmente las complejas relaciones entre las tablas de datos. A continuación se muestra el esquema de la base de datos, que es solo otra forma de decir el plano de la base de datos, que crearé en este artículo. Una vez que se sienta cómodo con el siguiente diagrama entidad-relación (ERD), es probable que desee agregar más al esquema, como datos fundamentales y económicos.

    Con el diseño de la base de datos completo, es hora de instalarla y configurarla.

    Instalación de PostgreSQL

    PostgreSQL es mi base de datos preferida. Es gratis y podría decirse que es la base de datos de código abierto más avanzada del mundo. Voy a repasar cómo instalar y configurar PostgreSQL, pero hay muchas versiones alojadas disponibles, como ElephantSQL, si prefiere sus datos en la nube. Uso la opción local para mis bases de datos de investigación porque es más rápida y me da más flexibilidad.

    A diferencia de la instalación de Zipline, hay muchos recursos disponibles que muestran cómo instalar PostgreSQL. Entonces, en lugar de duplicar el trabajo, siga una de las dos guías:

  • Cómo instalar y usar PostgreSQL en Ubuntu
  • Guías de instalación detalladas de PostgreSQL Wiki

    • Puede ejecutar PgAdmin en modo independiente o de servidor. Si solo está accediendo a PgAdmin en la computadora en la que se está ejecutando, use el método independiente. Si está accediendo a PostgreSQL y PgAdmin desde una computadora diferente, deberá ejecutar algunos comandos más para configurarlo como un servidor.

    Para aquellos de ustedes que ejecutan macOS o Windows y desean una instalación independiente, lo tienen fácil. Descargar PgAdmin, e instalarlo. Eso es. Si está ejecutando Linux, que es muy probable que sea el caso si está realizando una investigación avanzada, hay muchos artículos de configuración de PgAdmin disponibles con mis favoritos que se enumeran a continuación:

    Instalación de PgAdmin (independiente)

    El wiki de Postgresql proporciona instrucciones y un script para agregar su repositorio Apt instalar PgAdmin4 en Ubuntu.

    Instalación de PgAdmin (Servidor)

    Digital Ocean proporciona indicaciones sobre cómo instalar PgAdmin4 en modo servidor.

    Después de haber instalado PgAdmin, y si va a configurar sus tablas gráficamente, querrá mostrar los tipos de sistemas para que pueda usar los campos enumerados. Puede hacer esto con la siguiente ruta de clic en PgAdmin:

    File → Preferences → Browser → Display → Show system objects = True

    Crear la base de datos de acciones gráficamente

    Con un plano de las tablas, relaciones y columnas que tendrá nuestra base de datos, necesitamos introducir el esquema en PostgreSQL de alguna manera. Podemos hacer esto de dos maneras:

  • Usando una interfaz gráfica de usuario
  • Programáticamente

    • Crear la base de datos en PgAdmin

    Si es nuevo en bases de datos y PostgreSQL, esta es probablemente la ruta que querrá tomar. Puede iniciar sesión en PgAdmin y crear su esquema de base de datos a través de la interfaz gráfica de usuario (GUI). Puede hacer clic en la pestaña SQL para ver la instrucción SQL que creará la base de datos si está interesado en los detalles de SQL. Y si lo eres, te sugiero esto gran tutorial de PostgreSQL.

    Crear la base de datos programáticamente

    Si no está interesado en usar la GUI de PgAdmin, puede insertar el esquema directamente en PostgreSQL usando SQL o Python. Como con la mayoría de las cosas relacionadas con Python, hay varias formas de hacerlo. Recomiendo aprender SQLAlchemy ya que está en el mapeador relacional de objetos (ORM) que se encuentra encima de los adaptadores de bases de datos como psicopg2 y abstrae gran parte de los detalles de la base de datos fuera de la ecuación. En resumen, una vez que aprenda SQLAlchemy, puede cambiar la base de datos subyacente con bastante facilidad. El único inconveniente es que si desea modificar la base de datos, deberá importar otra biblioteca o ejecutar el comando modificar usando motor.ejecutar.

    Crear la base de datos usando SQLAlchemy

    Primero tenemos que conectarnos a la base de datos. Puedes hacer esto tan simple o sofisticado como quieras. Datacamp usa Python con PostgreSQL es un gran artículo que explica cómo crear una sesión de base de datos, y auth0 proporciona un gran tutorial sobre SQLAlchemy. Querrá asegurarse de haber seleccionado el entorno de Python apropiado usando conda o pip. Si no está seguro de cómo hacer esto, consulte mi publicación sobre Python Virtual Environments.

    Cree setup_psql_environment.yaml para almacenar sus credenciales. YAML hace un buen trabajo almacenando información de configuración. Es probable que también desee agregar este archivo a su .gitignore para no hacer públicas sus credenciales accidentalmente.

    PGHOST: localhost
PGDATABASE: your_database_here
PGUSER: your_user_here
PGPASSWORD: your_password_here
PGPORT: 5432

    El archivo setup_psql_enironment.py importa todos los módulos que necesitamos para conectarnos a Postgres.

    from sqlalchemy import create_engine
import yaml
import logging
log = logging.getLogger(__name__)

def get_database():
    try:
        engine = get_connection_from_profile()
        log.info("Connected to PostgreSQL database!")
    except IOError:
        log.exception("Failed to get database connection!")
        return None, 'fail'
    return engine

def get_connection_from_profile(config_file_name="setup_psql_environment.yaml"):
    """
    Sets up database connection from config file.
    Input:
    config_file_name: File containing PGHOST, PGUSER,
                        PGPASSWORD, PGDATABASE, PGPORT, which are the
                        credentials for the PostgreSQL database
    """
    with open(config_file_name, 'r') as f:
        vals = yaml.safe_load(f)
    if not ('PGHOST' in vals.keys() and
            'PGUSER' in vals.keys() and
            'PGPASSWORD' in vals.keys() and
            'PGDATABASE' in vals.keys() and
            'PGPORT' in vals.keys()):
        raise Exception('Bad config file: ' + config_file_name)
    return get_engine(vals['PGDATABASE'], vals['PGUSER'],
                        vals['PGHOST'], vals['PGPORT'],
                        vals['PGPASSWORD'])

def get_engine(db, user, host, port, passwd):
    """
    Get SQLalchemy engine using credentials.
    Input:
    db: database name
    user: Username
    host: Hostname of the database server
    port: Port number
    passwd: Password for the database
    """
    url = 'postgresql://{user}:{passwd}@{host}:{port}/{db}'.format(
        user=user, passwd=passwd, host=host, port=port, db=db)
    engine = create_engine(url, pool_size = 50, echo=True)
    return engine

    Cree un archivo models.py que definirá nuestro esquema y asignará nuestras clases de Python a las tablas de PostgreSQL. Querrá ajustar su modelo según sus propias preferencias, como usar tipos ENUM para company.industry_category o agregar una tabla para los fundamentos trimestrales. Recuerde, ¡esto es solo para comenzar!

    from sqlalchemy import Column, ForeignKey, Boolean, String, \
                       Integer, BigInteger, Float, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Enum, UniqueConstraint
import enum

Base = declarative_base()

class PriceFrequency(enum.Enum):
    daily = 'daily'
    weekly = 'weekly'
    monthly = 'monthly'
    quarterly = 'quarterly'
    yearly = 'yearly'

class Security(Base):
    __tablename__ = 'security'
    id = Column(Integer, primary_key=True, autoincrement=True)
    id_intrinio = Column('id_intrinio', String(10), unique=True, nullable=False)
    code = Column('code', String(3), nullable=False)
    currency = Column('currency', String(3), nullable=False)
    ticker = Column('ticker', String(12), nullable=False)
    name = Column('name', String(200), nullable=False)
    figi = Column('figi', String(12))
    composite_figi = Column('composite_figi', String(12))
    share_class_figi = Column('share_class_figi', String(12))
    exchange_id = Column(Integer, ForeignKey('exchange.id',
                                    onupdate="CASCADE",
                                    ondelete="SET NULL"))
    has_invalid_data = Column('has_invalid_data', Boolean)
    has_missing_company = Column('has_missing_company', Boolean)
    exchange = relationship('Exchange')
    company = relationship('Company')

class Exchange(Base):
    __tablename__ = 'exchange'
    id = Column(Integer, primary_key=True, autoincrement=True)
    mic = Column('mic', String(10), unique=True, nullable=False)
    acronym = Column('acronym', String(20))
    name = Column('name', String(200), nullable=False)
    security = relationship('Security')

class SecurityPrice(Base):
    __tablename__ = 'security_price'
    id = Column(Integer, primary_key=True)
    date = Column('date', Date, nullable=False)
    open = Column('open', Float)
    high = Column('high', Float)
    low = Column('low', Float)
    close = Column('close', Float)
    volume = Column('volume', BigInteger)
    adj_open = Column('adj_open', Float)
    adj_high = Column('adj_high', Float)
    adj_low = Column('adj_low', Float)
    adj_close = Column('adj_close', Float)
    adj_volume = Column('adj_volume', BigInteger)
    intraperiod = Column('intraperiod', Boolean, nullable=False)
    frequency = Column('frequency', Enum(PriceFrequency),nullable=False)
    security_id = Column(Integer, ForeignKey('security.id',
                                    onupdate="CASCADE",
                                    ondelete="CASCADE"),
                                    nullable=False)
    UniqueConstraint('date', 'security_id')
    security = relationship('Security')

class StockAdjustment(Base):
    __tablename__ = 'stock_adjustment'
    id = Column(Integer, primary_key=True)
    date = Column('date', Date, nullable=False)
    factor = Column('factor', Float, nullable=False)
    dividend = Column('dividend', Float)
    split_ratio = Column('split_ratio', Float)
    security_id = Column(Integer, ForeignKey('security.id',
                                    onupdate="CASCADE",
                                    ondelete="CASCADE"),
                                    nullable=False)
    security = relationship('Security')

class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(100), nullable=False)
    cik = Column('cik', String(10))
    description = Column('description', String(2000))
    company_url = Column('company_url', String(100))
    sic = Column('sic', String(4))
    employees = Column('employees', Integer)
    sector = Column('sector', String(200))
    industry_category = Column('industry_category', String(200))
    industry_group = Column('industry_group', String(200))
    security_id = Column(Integer, ForeignKey('security.id',
                                    onupdate="CASCADE",
                                    ondelete="CASCADE"),
                                    nullable=False)
    security = relationship('Security')

    Con las tablas de la base de datos definidas, ahora podemos decirle a SQLAlchemy que cree nuestra base de datos. Puede ejecutar create_psql_database.py o ejecutar el código desde Python REPL, que es solo una forma elegante de decir la línea de comando de Python.

    from sqlalchemy import MetaData
from sqlalchemy.orm import sessionmaker
from setup_psql_environment import get_database
from sqlalchemy.ext.declarative import declarative_base

import models

# Setup environment and create a session
db = get_database()
Session = sessionmaker(bind=db)
meta = MetaData(bind=db)
session = Session()

# Create database from SQLAlchemy models
models.Base.metadata.create_all(db)

    Creando la base de datos usando SQL

    Si no está interesado en usar SQLAlchemy, puede crear las tablas usando SQL y cambiar los nombres cuando corresponda.

    -- Table: public.exchange
-- DROP TABLE public.exchange;

CREATE TABLE public.exchange
(
    id integer NOT NULL DEFAULT nextval('exchange_id_seq'::regclass),
    mic character varying(10) COLLATE pg_catalog."default" NOT NULL,
    acronym character varying(20) COLLATE pg_catalog."default",
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT exchange_pkey PRIMARY KEY (id),
    CONSTRAINT exchange_mic_key UNIQUE (mic)

)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.exchange
    OWNER to username;


-- Table: public.security
-- DROP TABLE public.security;

CREATE TABLE public.security
(
    id integer NOT NULL DEFAULT nextval('security_id_seq'::regclass),
    id_intrinio character varying(10) COLLATE pg_catalog."default" NOT NULL,
    code character varying(3) COLLATE pg_catalog."default" NOT NULL,
    currency character varying(3) COLLATE pg_catalog."default" NOT NULL,
    ticker character varying(12) COLLATE pg_catalog."default" NOT NULL,
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    figi character varying(12) COLLATE pg_catalog."default",
    composite_figi character varying(12) COLLATE pg_catalog."default",
    share_class_figi character varying(12) COLLATE pg_catalog."default",
    exchange_id integer,
    has_invalid_data boolean,
    has_missing_company boolean,
    CONSTRAINT security_pkey PRIMARY KEY (id),
    CONSTRAINT security_id_intrinio_key UNIQUE (id_intrinio)
,
    CONSTRAINT security_exchange_id_fkey FOREIGN KEY (exchange_id)
        REFERENCES public.exchange (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE SET NULL
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.security
    OWNER to username;


-- Table: public.security_price
-- DROP TABLE public.security_price;

CREATE TABLE public.security_price
(
    id integer NOT NULL DEFAULT nextval('security_price_id_seq'::regclass),
    date date NOT NULL,
    open double precision,
    high double precision,
    low double precision,
    close double precision,
    volume bigint,
    adj_open double precision,
    adj_high double precision,
    adj_low double precision,
    adj_close double precision,
    adj_volume bigint,
    intraperiod boolean NOT NULL,
    frequency pricefrequency NOT NULL,
    security_id integer NOT NULL,
    CONSTRAINT security_price_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.security_price
    OWNER to username;


-- Table: public.stock_adjustment
-- DROP TABLE public.stock_adjustment;

CREATE TABLE public.stock_adjustment
(
    id integer NOT NULL DEFAULT nextval('stock_adjustment_id_seq'::regclass),
    date date NOT NULL,
    factor double precision NOT NULL,
    dividend double precision,
    split_ratio double precision,
    security_id integer NOT NULL,
    CONSTRAINT stock_adjustment_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.stock_adjustment
    OWNER to username;


-- Table: public.company
-- DROP TABLE public.company;

CREATE TABLE public.company
(
    id integer NOT NULL DEFAULT nextval('company_id_seq'::regclass),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    cik character varying(10) COLLATE pg_catalog."default",
    description character varying(2000) COLLATE pg_catalog."default",
    company_url character varying(100) COLLATE pg_catalog."default",
    sic character varying(4) COLLATE pg_catalog."default",
    employees integer,
    sector character varying(200) COLLATE pg_catalog."default",
    industry_category character varying(200) COLLATE pg_catalog."default",
    industry_group character varying(200) COLLATE pg_catalog."default",
    security_id integer NOT NULL,
    CONSTRAINT company_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.company
    OWNER to username;

    Related Posts:

    Deja un comentario