create_db.sql

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS "portfolio"
(
    "id"         INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "owner_name" TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS "transactions"
(
    "id"           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "portfolio_id" TEXT    NOT NULL,
    "position_id"  INTEGER,
    "type"         INTEGER NOT NULL,
    "quantity"     INTEGER NOT NULL CHECK (quantity >= 0),
    "price"        REAL    NOT NULL CHECK (price >= 0),
    "timestamp"    TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("portfolio_id") REFERENCES "portfolio" ("id") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ("type") REFERENCES "transaction_type" ("id") ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY ("position_id") REFERENCES "position" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS "position"
(
    "id"           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "portfolio_id" INTEGER NOT NULL,
    "ticker"       TEXT    NOT NULL,
    FOREIGN KEY ("portfolio_id") REFERENCES "portfolio" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS "transaction_type"
(
    "id"   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "name" TEXT    NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS "historical_marginal_tax_rates"
(
    "tax_year"          TEXT    NOT NULL,
    "bracket_threshold" INTEGER NOT NULL,
    "tax_rate"          REAL    NOT NULL,
    PRIMARY KEY ("tax_year", "bracket_threshold")
);

CREATE TABLE IF NOT EXISTS "historical_capital_gains_tax_rates"
(
    "tax_year"          TEXT    NOT NULL,
    "bracket_threshold" INTEGER NOT NULL,
    "tax_rate"          REAL    NOT NULL,
    PRIMARY KEY ("tax_year", "bracket_threshold")
);

CREATE TABLE IF NOT EXISTS "cpi_data"
(
    "date" TEXT NOT NULL PRIMARY KEY UNIQUE,
    "cpi"  REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS "daily_stock_data"
(
    "ticker"            TEXT    NOT NULL,
    "datetime"          TEXT    NOT NULL,
    "open"              REAL    NOT NULL,
    "close"             REAL    NOT NULL,
    "adjusted_close"    REAL    NOT NULL,
    "low"               REAL    NOT NULL,
    "high"              REAL    NOT NULL,
    "volume"            INTEGER NOT NULL,
    "dividend_amount"   REAL    NOT NULL,
    "split_coefficient" REAL    NOT NULL,
    "macd_histogram"    REAL,
    "macd_line"         REAL,
    "signal_line"       REAL,
    PRIMARY KEY ("ticker", "datetime")
);

CREATE TABLE IF NOT EXISTS "portfolio_report"
(
    "id"                           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "portfolio_id"                 INTEGER NOT NULL,
    "report_date"                  TEXT    NOT NULL,
    "net_pl"                       REAL    NOT NULL,
    "net_pl_percentage"            REAL    NOT NULL,
    "realised_pl"                  REAL    NOT NULL,
    "realised_pl_percentage"       REAL    NOT NULL,
    "closed_position_value"        REAL    NOT NULL,
    "closed_position_cost"         REAL    NOT NULL,
    "unrealised_pl"                REAL    NOT NULL,
    "unrealised_pl_percentage"     REAL NOT NULL,
    "open_position_value"          REAL NOT NULL,
    "open_position_cost"           REAL NOT NULL,
    "equity"                       REAL NOT NULL,
    "equity_change"                REAL NOT NULL,
    "cagr"                         REAL NOT NULL,
    "accounts_receivable"          REAL NOT NULL,
    "accounts_receivable_equities" REAL NOT NULL,
    "available_cash"               REAL NOT NULL,
    "net_contribution"             REAL NOT NULL,
    "deposits"                     REAL NOT NULL,
    "withdrawals"                  REAL NOT NULL,
    "net_income"                   REAL NOT NULL,
    "revenue"                      REAL NOT NULL,
    "revenue_equities"             REAL NOT NULL,
    "adjustments"                  REAL NOT NULL,
    "dividends"                    REAL NOT NULL,
    "cash_settlements"             REAL NOT NULL,
    "expenses"                     REAL NOT NULL,
    "taxes"                        REAL NOT NULL,
    "taxes_paid"                   REAL NOT NULL,
    "taxes_owing"                  REAL NOT NULL,
    "expenses_equities"            REAL NOT NULL,
    FOREIGN KEY ("portfolio_id") REFERENCES "portfolio" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "transactions_position_id_type_index" ON "transactions" ("position_id", "type");

CREATE INDEX IF NOT EXISTS "position_portfolio_id_index" ON "position" ("portfolio_id");

CREATE INDEX IF NOT EXISTS "transactions_portfolio_id_index" ON "transactions" ("portfolio_id");

CREATE INDEX IF NOT EXISTS "transactions_portfolio_id_type_index" ON "transactions" ("portfolio_id", "type");

CREATE INDEX IF NOT EXISTS "transactions_position_id_index" ON "transactions" ("position_id");

CREATE INDEX IF NOT EXISTS "daily_stock_data_dividend_amount_index" ON "daily_stock_data" ("dividend_amount" DESC);

CREATE INDEX IF NOT EXISTS "daily_stock_data_datetime_index" ON "daily_stock_data" ("datetime" ASC);

CREATE INDEX IF NOT EXISTS "daily_stock_data_ticker_index" ON "daily_stock_data" ("ticker" ASC);

CREATE INDEX IF NOT EXISTS "historical_marginal_tax_rates_tax_year_bracket_threshold_tax_rate_index"
    ON "historical_marginal_tax_rates" ("tax_year", "bracket_threshold", "tax_rate");

CREATE INDEX IF NOT EXISTS "historical_capital_gains_tax_rates_tax_year_bracket_threshold_tax_rate_index"
    ON "historical_capital_gains_tax_rates" ("tax_year", "bracket_threshold", "tax_rate");

CREATE TRIGGER IF NOT EXISTS transactions_position_id_on_update
    BEFORE UPDATE
    ON transactions
    WHEN (NEW.type IN (3, 4, 5, 6) AND NEW.position_id IS NULL)
BEGIN
    SELECT RAISE(ABORT, 'Transactions dealing with positions must specify a position ID.');
END;

CREATE TRIGGER IF NOT EXISTS transactions_position_id_on_insert
    BEFORE INSERT
    ON transactions
    WHEN (NEW.type IN (3, 4, 5, 6) AND NEW.position_id IS NULL)
BEGIN
    SELECT RAISE(ABORT, 'Transactions dealing with positions must specify a position ID.');
END;

DROP VIEW IF EXISTS portfolio_balance;

CREATE VIEW IF NOT EXISTS portfolio_balance
            (
             portfolio_id,
             balance
                )
AS
SELECT portfolio.id,
       (sums.total_in - sums.total_out)
FROM portfolio
         INNER JOIN (
    SELECT "inner".id as id,
           (SELECT IFNULL(SUM(quantity * price), 0)
            FROM transactions
            WHERE transactions.portfolio_id = "inner".id
              AND transactions.type IN (SELECT transaction_type.id
                                        FROM transaction_type
                                        WHERE transaction_type.name NOT IN ('WITHDRAWAL', 'BUY', 'TAX'))
           )          AS total_in,
           (SELECT IFNULL(SUM(quantity * price), 0)
            FROM transactions
            WHERE transactions.portfolio_id = "inner".id
              AND transactions.type IN (SELECT transaction_type.id
                                        FROM transaction_type
                                        WHERE transaction_type.name IN ('WITHDRAWAL', 'BUY', 'TAX'))
           )          AS total_out
    FROM portfolio AS "inner"
) sums
                    ON sums.id = portfolio.id;

INSERT OR
REPLACE
INTO "transaction_type" ("id", "name")
VALUES (1, 'DEPOSIT'),
       (2, 'WITHDRAWAL'),
       (3, 'BUY'),
       (4, 'SELL'),
       (5, 'DIVIDEND'),
       (6, 'CASH_SETTLEMENT'),
       (7, 'TAX');

INSERT OR
REPLACE
INTO "historical_marginal_tax_rates" (tax_year, bracket_threshold, tax_rate)
VALUES ('2000-01-01 00:00:00', 0, 0.15),
       ('2000-01-01 00:00:00', 26250, 0.28),
       ('2000-01-01 00:00:00', 63550, 0.31),
       ('2000-01-01 00:00:00', 132600, 0.36),
       ('2000-01-01 00:00:00', 288350, 0.396),
       ('2001-01-01 00:00:00', 0, 0.15),
       ('2001-01-01 00:00:00', 27050, 0.275),
       ('2001-01-01 00:00:00', 65550, 0.305),
       ('2001-01-01 00:00:00', 136750, 0.355),
       ('2001-01-01 00:00:00', 297350, 0.391),
       ('2002-01-01 00:00:00', 0, 0.10),
       ('2002-01-01 00:00:00', 6000, 0.15),
       ('2002-01-01 00:00:00', 27950, 0.27),
       ('2002-01-01 00:00:00', 67700, 0.30),
       ('2002-01-01 00:00:00', 141250, 0.35),
       ('2002-01-01 00:00:00', 307050, 0.386),
       ('2003-01-01 00:00:00', 0, 0.10),
       ('2003-01-01 00:00:00', 7000, 0.15),
       ('2003-01-01 00:00:00', 28400, 0.25),
       ('2003-01-01 00:00:00', 68800, 0.28),
       ('2003-01-01 00:00:00', 143500, 0.33),
       ('2003-01-01 00:00:00', 311950, 0.35),
       ('2004-01-01 00:00:00', 0, 0.10),
       ('2004-01-01 00:00:00', 7150, 0.15),
       ('2004-01-01 00:00:00', 29050, 0.25),
       ('2004-01-01 00:00:00', 70350, 0.28),
       ('2004-01-01 00:00:00', 146750, 0.33),
       ('2004-01-01 00:00:00', 319100, 0.35),
       ('2005-01-01 00:00:00', 0, 0.10),
       ('2005-01-01 00:00:00', 7300, 0.15),
       ('2005-01-01 00:00:00', 29700, 0.25),
       ('2005-01-01 00:00:00', 71950, 0.28),
       ('2005-01-01 00:00:00', 150150, 0.33),
       ('2005-01-01 00:00:00', 326450, 0.35),
       ('2006-01-01 00:00:00', 0, 0.10),
       ('2006-01-01 00:00:00', 7550, 0.15),
       ('2006-01-01 00:00:00', 30650, 0.25),
       ('2006-01-01 00:00:00', 74200, 0.28),
       ('2006-01-01 00:00:00', 154800, 0.33),
       ('2006-01-01 00:00:00', 336550, 0.35),
       ('2007-01-01 00:00:00', 0, 0.10),
       ('2007-01-01 00:00:00', 7825, 0.15),
       ('2007-01-01 00:00:00', 31850, 0.25),
       ('2007-01-01 00:00:00', 77100, 0.28),
       ('2007-01-01 00:00:00', 160850, 0.33),
       ('2007-01-01 00:00:00', 349700, 0.35),
       ('2008-01-01 00:00:00', 0, 0.10),
       ('2008-01-01 00:00:00', 8025, 0.15),
       ('2008-01-01 00:00:00', 32550, 0.25),
       ('2008-01-01 00:00:00', 78850, 0.28),
       ('2008-01-01 00:00:00', 164550, 0.33),
       ('2008-01-01 00:00:00', 357700, 0.35),
       ('2009-01-01 00:00:00', 0, 0.10),
       ('2009-01-01 00:00:00', 8350, 0.15),
       ('2009-01-01 00:00:00', 33950, 0.25),
       ('2009-01-01 00:00:00', 82250, 0.28),
       ('2009-01-01 00:00:00', 171550, 0.33),
       ('2009-01-01 00:00:00', 372950, 0.35),
       ('2010-01-01 00:00:00', 0, 0.10),
       ('2010-01-01 00:00:00', 8375, 0.15),
       ('2010-01-01 00:00:00', 34000, 0.25),
       ('2010-01-01 00:00:00', 82400, 0.28),
       ('2010-01-01 00:00:00', 171850, 0.33),
       ('2010-01-01 00:00:00', 373650, 0.35),
       ('2011-01-01 00:00:00', 0, 0.10),
       ('2011-01-01 00:00:00', 8500, 0.15),
       ('2011-01-01 00:00:00', 34500, 0.25),
       ('2011-01-01 00:00:00', 83600, 0.28),
       ('2011-01-01 00:00:00', 174400, 0.33),
       ('2011-01-01 00:00:00', 379150, 0.35),
       ('2012-01-01 00:00:00', 0, 0.10),
       ('2012-01-01 00:00:00', 8700, 0.15),
       ('2012-01-01 00:00:00', 35350, 0.25),
       ('2012-01-01 00:00:00', 85650, 0.28),
       ('2012-01-01 00:00:00', 178650, 0.33),
       ('2012-01-01 00:00:00', 388350, 0.35),
       ('2013-01-01 00:00:00', 0, 0.10),
       ('2013-01-01 00:00:00', 8925, 0.15),
       ('2013-01-01 00:00:00', 36250, 0.25),
       ('2013-01-01 00:00:00', 87850, 0.28),
       ('2013-01-01 00:00:00', 183250, 0.33),
       ('2013-01-01 00:00:00', 398350, 0.35),
       ('2013-01-01 00:00:00', 400000, 0.396),
       ('2014-01-01 00:00:00', 0, 0.10),
       ('2014-01-01 00:00:00', 9075, 0.15),
       ('2014-01-01 00:00:00', 36900, 0.25),
       ('2014-01-01 00:00:00', 89350, 0.28),
       ('2014-01-01 00:00:00', 186350, 0.33),
       ('2014-01-01 00:00:00', 405100, 0.35),
       ('2014-01-01 00:00:00', 406750, 0.396),
       ('2015-01-01 00:00:00', 0, 0.10),
       ('2015-01-01 00:00:00', 9225, 0.15),
       ('2015-01-01 00:00:00', 37450, 0.25),
       ('2015-01-01 00:00:00', 90750, 0.28),
       ('2015-01-01 00:00:00', 189300, 0.33),
       ('2015-01-01 00:00:00', 411500, 0.35),
       ('2015-01-01 00:00:00', 413200, 0.396),
       ('2016-01-01 00:00:00', 0, 0.10),
       ('2016-01-01 00:00:00', 9275, 0.15),
       ('2016-01-01 00:00:00', 37650, 0.25),
       ('2016-01-01 00:00:00', 91150, 0.28),
       ('2016-01-01 00:00:00', 190151, 0.33),
       ('2016-01-01 00:00:00', 413350, 0.35),
       ('2016-01-01 00:00:00', 415050, 0.396),
       ('2017-01-01 00:00:00', 0, 0.10),
       ('2017-01-01 00:00:00', 9325, 0.15),
       ('2017-01-01 00:00:00', 37950, 0.25),
       ('2017-01-01 00:00:00', 91900, 0.28),
       ('2017-01-01 00:00:00', 191650, 0.33),
       ('2017-01-01 00:00:00', 416700, 0.35),
       ('2017-01-01 00:00:00', 418400, 0.396),
       ('2018-01-01 00:00:00', 0, 0.10),
       ('2018-01-01 00:00:00', 9525, 0.12),
       ('2018-01-01 00:00:00', 38700, 0.22),
       ('2018-01-01 00:00:00', 82500, 0.24),
       ('2018-01-01 00:00:00', 157500, 0.32),
       ('2018-01-01 00:00:00', 200000, 0.35),
       ('2018-01-01 00:00:00', 500000, 0.37),
       ('2019-01-01 00:00:00', 0, 0.10),
       ('2019-01-01 00:00:00', 9700, 0.12),
       ('2019-01-01 00:00:00', 39475, 0.22),
       ('2019-01-01 00:00:00', 84200, 0.24),
       ('2019-01-01 00:00:00', 160725, 0.32),
       ('2019-01-01 00:00:00', 204100, 0.35),
       ('2019-01-01 00:00:00', 510300, 0.37);

INSERT OR
REPLACE
INTO "historical_capital_gains_tax_rates" (tax_year, bracket_threshold, tax_rate)
VALUES ('2000-01-01 00:00:00', 0, 0.10),
       ('2000-01-01 00:00:00', 26250, 0.20),
       ('2001-01-01 00:00:00', 0, 0.10),
       ('2001-01-01 00:00:00', 27050, 0.20),
       ('2002-01-01 00:00:00', 0, 0.10),
       ('2002-01-01 00:00:00', 27950, 0.20),
       ('2003-01-01 00:00:00', 0, 0.10),
       ('2003-01-01 00:00:00', 7000, 0.10),
       ('2003-01-01 00:00:00', 28400, 0.15),
       ('2004-01-01 00:00:00', 0, 0.10),
       ('2004-01-01 00:00:00', 29050, 0.15),
       ('2005-01-01 00:00:00', 0, 0.10),
       ('2005-01-01 00:00:00', 29700, 0.15),
       ('2006-01-01 00:00:00', 0, 0.10),
       ('2006-01-01 00:00:00', 30650, 0.15),
       ('2007-01-01 00:00:00', 0, 0.10),
       ('2007-01-01 00:00:00', 31850, 0.15),
       ('2008-01-01 00:00:00', 0, 0.00),
       ('2008-01-01 00:00:00', 32550, 0.15),
       ('2009-01-01 00:00:00', 0, 0.00),
       ('2009-01-01 00:00:00', 33950, 0.15),
       ('2010-01-01 00:00:00', 0, 0.00),
       ('2010-01-01 00:00:00', 34000, 0.15),
       ('2011-01-01 00:00:00', 0, 0.00),
       ('2011-01-01 00:00:00', 34500, 0.15),
       ('2012-01-01 00:00:00', 0, 0.00),
       ('2012-01-01 00:00:00', 35350, 0.15),
       ('2013-01-01 00:00:00', 0, 0.0),
       ('2013-01-01 00:00:00', 36250, 0.15),
       ('2013-01-01 00:00:00', 400000, 0.20),
       ('2014-01-01 00:00:00', 0, 0.10),
       ('2014-01-01 00:00:00', 36900, 0.15),
       ('2014-01-01 00:00:00', 406750, 0.20),
       ('2015-01-01 00:00:00', 0, 0.10),
       ('2015-01-01 00:00:00', 37450, 0.15),
       ('2015-01-01 00:00:00', 413200, 0.20),
       ('2016-01-01 00:00:00', 0, 0.10),
       ('2016-01-01 00:00:00', 37650, 0.15),
       ('2016-01-01 00:00:00', 415050, 0.20),
       ('2017-01-01 00:00:00', 0, 0.10),
       ('2017-01-01 00:00:00', 37950, 0.15),
       ('2017-01-01 00:00:00', 418400, 0.20),
       ('2018-01-01 00:00:00', 0, 0.00),
       ('2018-01-01 00:00:00', 38600, 0.15),
       ('2018-01-01 00:00:00', 425800, 0.20),
       ('2019-01-01 00:00:00', 0, 0.00),
       ('2019-01-01 00:00:00', 39375, 0.15),
       ('2019-01-01 00:00:00', 434550, 0.20);

INSERT
    OR
REPLACE
INTO "cpi_data"
VALUES ('2000-01-01 00:00:00', 65.627818730366101361),
       ('2000-02-01 00:00:00', 66.016609125688177073),
       ('2000-03-01 00:00:00', 66.5609156791390717),
       ('2000-04-01 00:00:00', 66.599794718671276429),
       ('2000-05-01 00:00:00', 66.677552797735700094),
       ('2000-06-01 00:00:00', 67.027464153525556866),
       ('2000-07-01 00:00:00', 67.182980311654389993),
       ('2000-08-01 00:00:00', 67.182980311654389993),
       ('2000-09-01 00:00:00', 67.532891667444246764),
       ('2000-10-01 00:00:00', 67.649528786040875162),
       ('2000-11-01 00:00:00', 67.688407825573079891),
       ('2000-12-01 00:00:00', 67.649528786040875162),
       ('2001-01-01 00:00:00', 68.077198220895155599),
       ('2001-02-01 00:00:00', 68.349351497620617124),
       ('2001-03-01 00:00:00', 68.50486765574943604),
       ('2001-04-01 00:00:00', 68.777020932474897561),
       ('2001-05-01 00:00:00', 69.088053248732549604),
       ('2001-06-01 00:00:00', 69.20469036732916379),
       ('2001-07-01 00:00:00', 69.010295169668140146),
       ('2001-08-01 00:00:00', 69.010295169668140146),
       ('2001-09-01 00:00:00', 69.321327485925792188),
       ('2001-10-01 00:00:00', 69.088053248732549604),
       ('2001-11-01 00:00:00', 68.971416130135935417),
       ('2001-12-01 00:00:00', 68.699262853410473896),
       ('2002-01-01 00:00:00', 68.854779011539307019),
       ('2002-02-01 00:00:00', 69.126932288264768544),
       ('2002-03-01 00:00:00', 69.515722683586830044),
       ('2002-04-01 00:00:00', 69.904513078908919964),
       ('2002-05-01 00:00:00', 69.904513078908919964),
       ('2002-06-01 00:00:00', 69.943392118441110481),
       ('2002-07-01 00:00:00', 70.021150197505519939),
       ('2002-08-01 00:00:00', 70.254424434698776735),
       ('2002-09-01 00:00:00', 70.371061553295405134),
       ('2002-10-01 00:00:00', 70.48769867189201932),
       ('2002-11-01 00:00:00', 70.48769867189201932),
       ('2002-12-01 00:00:00', 70.332182513763186193),
       ('2003-01-01 00:00:00', 70.643214830020838236),
       ('2003-02-01 00:00:00', 71.18752138347174707),
       ('2003-03-01 00:00:00', 71.615190818326027511),
       ('2003-04-01 00:00:00', 71.459674660197208596),
       ('2003-05-01 00:00:00', 71.343037541600580198),
       ('2003-06-01 00:00:00', 71.420795620664989655),
       ('2003-07-01 00:00:00', 71.498553699729413325),
       ('2003-08-01 00:00:00', 71.770706976454860638),
       ('2003-09-01 00:00:00', 72.003981213648103219),
       ('2003-10-01 00:00:00', 71.926223134583693761),
       ('2003-11-01 00:00:00', 71.73182793692265591),
       ('2003-12-01 00:00:00', 71.654069857858246452),
       ('2004-01-01 00:00:00', 72.003981213648103219),
       ('2004-02-01 00:00:00', 72.392771608970178931),
       ('2004-03-01 00:00:00', 72.859320083356664101),
       ('2004-04-01 00:00:00', 73.092594320549906681),
       ('2004-05-01 00:00:00', 73.520263755404187122),
       ('2004-06-01 00:00:00', 73.753537992597443919),
       ('2004-07-01 00:00:00', 73.63690087400081552),
       ('2004-08-01 00:00:00', 73.675779913533020249),
       ('2004-09-01 00:00:00', 73.831296071661853376),
       ('2004-10-01 00:00:00', 74.220086466983929084),
       ('2004-11-01 00:00:00', 74.258965506516133813),
       ('2004-12-01 00:00:00', 73.986812229790686499),
       ('2005-01-01 00:00:00', 74.142328387919505415),
       ('2005-02-01 00:00:00', 74.569997822773800067),
       ('2005-03-01 00:00:00', 75.153183415756913631),
       ('2005-04-01 00:00:00', 75.658610929675589318),
       ('2005-05-01 00:00:00', 75.580852850611194072),
       ('2005-06-01 00:00:00', 75.619731890143398801),
       ('2005-07-01 00:00:00', 75.969643245933255572),
       ('2005-08-01 00:00:00', 76.358433641255345492),
       ('2005-09-01 00:00:00', 77.291530590028315827),
       ('2005-10-01 00:00:00', 77.447046748157134742),
       ('2005-11-01 00:00:00', 76.824982115641830661),
       ('2005-12-01 00:00:00', 76.513949799384178619),
       ('2006-01-01 00:00:00', 77.097135392367277971),
       ('2006-02-01 00:00:00', 77.252651550496096886),
       ('2006-03-01 00:00:00', 77.680320985350391539),
       ('2006-04-01 00:00:00', 78.34126465739791456),
       ('2006-05-01 00:00:00', 78.73005505271997606),
       ('2006-06-01 00:00:00', 78.885571210848809188),
       ('2006-07-01 00:00:00', 79.11884544804206598),
       ('2006-08-01 00:00:00', 79.274361606170899107),
       ('2006-09-01 00:00:00', 78.885571210848809188),
       ('2006-10-01 00:00:00', 78.457901775994542958),
       ('2006-11-01 00:00:00', 78.34126465739791456),
       ('2006-12-01 00:00:00', 78.457901775994542958),
       ('2007-01-01 00:00:00', 78.697396659512932615),
       ('2007-02-01 00:00:00', 79.118456657646746067),
       ('2007-03-01 00:00:00', 79.838885260178543033),
       ('2007-04-01 00:00:00', 80.357531647538181117),
       ('2007-05-01 00:00:00', 80.848573916829963078),
       ('2007-06-01 00:00:00', 81.005256446144770169),
       ('2007-07-01 00:00:00', 80.984650555192700949),
       ('2007-08-01 00:00:00', 80.836132624179654728),
       ('2007-09-01 00:00:00', 81.058909520699216951),
       ('2007-10-01 00:00:00', 81.232310037012851466),
       ('2007-11-01 00:00:00', 81.714798917607552649),
       ('2007-12-01 00:00:00', 81.659979471867131907),
       ('2008-01-01 00:00:00', 82.065876644583397592),
       ('2008-02-01 00:00:00', 82.304205156915813288),
       ('2008-03-01 00:00:00', 83.017635532331809141),
       ('2008-04-01 00:00:00', 83.521119094273899461),
       ('2008-05-01 00:00:00', 84.224440919411534874),
       ('2008-06-01 00:00:00', 85.073170352399614555),
       ('2008-07-01 00:00:00', 85.519890516624698763),
       ('2008-08-01 00:00:00', 85.178532549531908559),
       ('2008-09-01 00:00:00', 85.060729059749320413),
       ('2008-10-01 00:00:00', 84.20150228608753196),
       ('2008-11-01 00:00:00', 82.588799726291568957),
       ('2008-12-01 00:00:00', 81.734627227768967827),
       ('2009-01-01 00:00:00', 82.090370439488665969),
       ('2009-02-01 00:00:00', 82.498600354576851149),
       ('2009-03-01 00:00:00', 82.699216198563036076),
       ('2009-04-01 00:00:00', 82.905663898479062369),
       ('2009-05-01 00:00:00', 83.145158781997452026),
       ('2009-06-01 00:00:00', 83.85936673820410192),
       ('2009-07-01 00:00:00', 83.726400423003951804),
       ('2009-08-01 00:00:00', 83.914186183944522671),
       ('2009-09-01 00:00:00', 83.966672887313009709),
       ('2009-10-01 00:00:00', 84.047541289540006912),
       ('2009-11-01 00:00:00', 84.107026220024266646),
       ('2009-12-01 00:00:00', 83.958897079406568765),
       ('2010-01-01 00:00:00', 84.245824391154258137),
       ('2010-02-01 00:00:00', 84.266819072501647266),
       ('2010-03-01 00:00:00', 84.612842524338276462),
       ('2010-04-01 00:00:00', 84.759805293770043022),
       ('2010-05-01 00:00:00', 84.82551087057944983),
       ('2010-06-01 00:00:00', 84.742698516375853049),
       ('2010-07-01 00:00:00', 84.76058287456066864),
       ('2010-08-01 00:00:00', 84.877608783552631168),
       ('2010-09-01 00:00:00', 84.926985163758530461),
       ('2010-10-01 00:00:00', 85.032736151286130166),
       ('2010-11-01 00:00:00', 85.068504867655761357),
       ('2010-12-01 00:00:00', 85.214690056296859667),
       ('2011-01-01 00:00:00', 85.620587229013096928),
       ('2011-02-01 00:00:00', 86.042813598332884336),
       ('2011-03-01 00:00:00', 86.881823271437923494),
       ('2011-04-01 00:00:00', 87.441292650306365885),
       ('2011-05-01 00:00:00', 87.852632888557153027),
       ('2011-06-01 00:00:00', 87.758545612889193423),
       ('2011-07-01 00:00:00', 87.83630369195360288),
       ('2011-08-01 00:00:00', 88.078520108239260364),
       ('2011-09-01 00:00:00', 88.212264004230050316),
       ('2011-10-01 00:00:00', 88.030310099219306607),
       ('2011-11-01 00:00:00', 87.956051133712804812),
       ('2011-12-01 00:00:00', 87.739106093123083954),
       ('2012-01-01 00:00:00', 88.12517495567789183),
       ('2012-02-01 00:00:00', 88.513187770209327707),
       ('2012-03-01 00:00:00', 89.185406363721199344),
       ('2012-04-01 00:00:00', 89.45483810767940724),
       ('2012-05-01 00:00:00', 89.349864700942433163),
       ('2012-06-01 00:00:00', 89.218842337718896832),
       ('2012-07-01 00:00:00', 89.073434729868452564),
       ('2012-08-01 00:00:00', 89.56914248390410194),
       ('2012-09-01 00:00:00', 89.968819010295192125),
       ('2012-10-01 00:00:00', 89.933827874716200769),
       ('2012-11-01 00:00:00', 89.507713601443199989),
       ('2012-12-01 00:00:00', 89.266663556343516464),
       ('2013-01-01 00:00:00', 89.530652234767202912),
       ('2013-02-01 00:00:00', 90.263910920344642366),
       ('2013-03-01 00:00:00', 90.499906690305138567),
       ('2013-04-01 00:00:00', 90.405819414637178963),
       ('2013-05-01 00:00:00', 90.566778638300533543),
       ('2013-06-01 00:00:00', 90.784112469285574318),
       ('2013-07-01 00:00:00', 90.819881185655191302),
       ('2013-08-01 00:00:00', 90.929131286740698669),
       ('2013-09-01 00:00:00', 91.034882274268298374),
       ('2013-10-01 00:00:00', 90.800441665889096041),
       ('2013-11-01 00:00:00', 90.614988647320473093),
       ('2013-12-01 00:00:00', 90.607212839414032149),
       ('2014-01-01 00:00:00', 90.944294112158260649),
       ('2014-02-01 00:00:00', 91.280597804111849313),
       ('2014-03-01 00:00:00', 91.868448881838844499),
       ('2014-04-01 00:00:00', 92.171316599794721469),
       ('2014-05-01 00:00:00', 92.493235047121416412),
       ('2014-06-01 00:00:00', 92.665469192249076968),
       ('2014-07-01 00:00:00', 92.62931168548412586),
       ('2014-08-01 00:00:00', 92.474573108145946779),
       ('2014-09-01 00:00:00', 92.544166588908595375),
       ('2014-10-01 00:00:00', 92.311669932505992621),
       ('2014-11-01 00:00:00', 91.813240645703089624),
       ('2014-12-01 00:00:00', 91.292650306366851962),
       ('2015-01-01 00:00:00', 90.863036919535943528),
       ('2015-02-01 00:00:00', 91.257659170787860606),
       ('2015-03-01 00:00:00', 91.800799353052809689),
       ('2015-04-01 00:00:00', 91.987418742807392389),
       ('2015-05-01 00:00:00', 92.456299959565811262),
       ('2015-06-01 00:00:00', 92.780162358869105784),
       ('2015-07-01 00:00:00', 92.786383005194267067),
       ('2015-08-01 00:00:00', 92.654971851575368187),
       ('2015-09-01 00:00:00', 92.510730614910897887),
       ('2015-10-01 00:00:00', 92.469130042611439538),
       ('2015-11-01 00:00:00', 92.273957264159761852),
       ('2015-12-01 00:00:00', 91.9586482535535481),
       ('2016-01-01 00:00:00', 92.110665298124487776),
       ('2016-02-01 00:00:00', 92.186479425212283448),
       ('2016-03-01 00:00:00', 92.583434418836134229),
       ('2016-04-01 00:00:00', 93.02237877515474906),
       ('2016-05-01 00:00:00', 93.39872787782653063),
       ('2016-06-01 00:00:00', 93.705483499735635179),
       ('2016-07-01 00:00:00', 93.55385524556001542),
       ('2016-08-01 00:00:00', 93.639777922926214163),
       ('2016-09-01 00:00:00', 93.864887561817681674),
       ('2016-10-01 00:00:00', 93.981913470809629986),
       ('2016-11-01 00:00:00', 93.835728282168517468),
       ('2016-12-01 00:00:00', 93.866442723398961335),
       ('2017-01-01 00:00:00', 94.413470809617137998),
       ('2017-02-01 00:00:00', 94.710506671643187818),
       ('2017-03-01 00:00:00', 94.787487169916957441),
       ('2017-04-01 00:00:00', 95.068582625734819654),
       ('2017-05-01 00:00:00', 95.149839818357136774),
       ('2017-06-01 00:00:00', 95.236151286118641227),
       ('2017-07-01 00:00:00', 95.170445709309191787),
       ('2017-08-01 00:00:00', 95.45542906908028158),
       ('2017-09-01 00:00:00', 95.960856582998985686),
       ('2017-10-01 00:00:00', 95.900205281328737786),
       ('2017-11-01 00:00:00', 95.902538023700685696),
       ('2017-12-01 00:00:00', 95.846163416378971078),
       ('2018-01-01 00:00:00', 96.368308917296531031),
       ('2018-02-01 00:00:00', 96.80530932163851787),
       ('2018-03-01 00:00:00', 97.024198314204852522),
       ('2018-04-01 00:00:00', 97.409878386364354696),
       ('2018-05-01 00:00:00', 97.814997978289952131),
       ('2018-06-01 00:00:00', 97.970902926814105171),
       ('2018-07-01 00:00:00', 97.977512363534572156),
       ('2018-08-01 00:00:00', 98.031943018879658781),
       ('2018-09-01 00:00:00', 98.145858604709047771),
       ('2018-10-01 00:00:00', 98.319259121022682277),
       ('2018-11-01 00:00:00', 97.989953656184880514),
       ('2018-12-01 00:00:00', 97.676977387950628898),
       ('2019-01-01 00:00:00', 97.863207987309891681),
       ('2019-02-01 00:00:00', 98.276880967932569886),
       ('2019-03-01 00:00:00', 98.831296071661853376),
       ('2019-04-01 00:00:00', 99.354607943765373082),
       ('2019-05-01 00:00:00', 99.566109918820572492),
       ('2019-06-01 00:00:00', 99.585938228982001877),
       ('2019-07-01 00:00:00', 99.752340518179835274),
       ('2019-08-01 00:00:00', 99.747286243040662158),
       ('2019-09-01 00:00:00', 99.825433112500405741),
       ('2019-10-01 00:00:00', 100.05365307455444678),
       ('2019-11-01 00:00:00', 100.0),
       ('2019-12-01 00:00:00', 100.0);

COMMIT;