Ubuntu Server Python Pandas âSVD did not convergeâ
<p>I have the following code.</p>

<pre><code>#!/usr/bin/python
# -*- coding: utf-8 -*-
# cadf.py
import datetime
import MySQLdb as mdb
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import pandas as pd
import pprint
import statsmodels.tsa.stattools as ts

from pandas.stats.api import ols

if __name__ == "__main__":
    # Connect to the MySQL instance
    db_host = '192.168.200.128'
    db_user = 'sec_master'
    db_pass = 'pass'
    db_name = 'GBP-USD'
    con = mdb.connect(db_host, db_user, db_pass, db_name)

    sql = """SELECT TIME,`BID-CLOSE`
             FROM `GBP-USD`.`tbl_GBP-USD_1-Day`
             WHERE TIME &gt;= '2007-01-01' AND TIME &lt;= '2016-09-20'
             ORDER BY TIME ASC;"""
    # Create a pandas dataframe from the SQL query
    GBPUSD = pd.read_sql_query(sql, con=con, index_col='TIME')

if __name__ == "__main__":
    # Connect to the MySQL instance
    db_host2 = '192.168.200.128'
    db_user2 = 'sec_master'
    db_pass2 = 'pass'
    db_name2 = 'EUR-USD'
    con = mdb.connect(db_host2, db_user2, db_pass2, db_name2)

    sql2 = """SELECT TIME,`BID-CLOSE`
              FROM `EUR-USD`.`tbl_EUR-USD_1-Day`
              WHERE TIME &gt;= '2007-01-01' AND TIME &lt;= '2016-09-20'
              ORDER BY TIME ASC;"""
    # Create a pandas dataframe from the SQL query
    EURUSD = pd.read_sql_query(sql2, con=con, index_col='TIME')

def plot_price_series(df, ts1, ts2):
    months = mdates.MonthLocator() # every month
    fig, ax = plt.subplots()
    ax.plot(df.index, df[ts1], label=ts1)
    ax.plot(df.index, df[ts2], label=ts2)
    ax.xaxis.set_major_locator(months)
    ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))
    ax.set_xlim(datetime.datetime(2007, 1, 1), datetime.datetime(2016, 9, 20))
    ax.grid(True)
    fig.autofmt_xdate()
    plt.xlabel('Month/Year')
    plt.ylabel('Price ($)')
    plt.title('%s and %s Daily Prices' % (ts1, ts2))
    plt.legend()
    plt.show()

def plot_scatter_series(df, ts1, ts2):
    plt.xlabel('%s Price ($)' % ts1)
    plt.ylabel('%s Price ($)' % ts2)
    plt.title('%s and %s Price Scatterplot' % (ts1, ts2))
    plt.scatter(df[ts1], df[ts2])
    plt.show()

def plot_residuals(df):
    months = mdates.MonthLocator() # every month
    fig, ax = plt.subplots()
    ax.plot(df.index, df["res"], label="Residuals")
    ax.xaxis.set_major_locator(months)
    ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))
    ax.set_xlim(datetime.datetime(2007, 1, 1), datetime.datetime(2016, 9, 20))
    ax.grid(True)
    fig.autofmt_xdate()

    plt.xlabel('Month/Year')
    plt.ylabel('Price ($)')
    plt.title('Residual Plot')
    plt.legend()

    plt.plot(df["res"])
    plt.show()

if __name__ == "__main__":
    df = pd.DataFrame(index=GBPUSD.index)
    df["GBPUSD"] = GBPUSD["BID-CLOSE"]
    df["EURUSD"] = EURUSD["BID-CLOSE"]

    # Plot the two time series
    plot_price_series(df, "GBPUSD", "EURUSD")

    # Display a scatter plot of the two time series
    plot_scatter_series(df, "GBPUSD", "EURUSD")

    # Calculate optimal hedge ratio "beta"
    res = ols(y=df['EURUSD'], x=df["GBPUSD"])
    beta_hr = res.beta.x

    # Calculate the residuals of the linear combination
    df["res"] = df["EURUSD"] - beta_hr*df["GBPUSD"]

    # Plot the residuals
    plot_residuals(df)

    # Calculate and output the CADF test on the residuals
    cadf = ts.adfuller(df["res"])
    pprint.pprint(cadf)
</code></pre>

<p>I run the code on Ubuntu Server and receive the following error.</p>

<blockquote>
  <p>james@GEN-U-DAE-01:~/Desktop$ sudo python cadf_gbpusdAndEurusd2.py</p>
</blockquote>

<pre><code>sys:1: FutureWarning: The pandas.stats.ols module is deprecated and will be removed in a future version. We refer to external packages like statsmodels, see some examples here: http://statsmodels.sourceforge.net/stable/regression.html
Traceback (most recent call last):
  File "cadf_gbpusdAndEurusd2.py", line 110, in &lt;module&gt;
    cadf = ts.adfuller(df["res"])
  File "/usr/local/lib/python2.7/dist-packages/statsmodels/tsa/stattools.py", line 231, in adfuller
    maxlag, autolag)
  File "/usr/local/lib/python2.7/dist-packages/statsmodels/tsa/stattools.py", line 73, in _autolag
    results[lag] = mod_instance.fit()
  File "/usr/local/lib/python2.7/dist-packages/statsmodels/regression/linear_model.py", line 174, in fit
    self.pinv_wexog, singular_values = pinv_extended(self.wexog)
  File "/usr/local/lib/python2.7/dist-packages/statsmodels/tools/tools.py", line 392, in pinv_extended
    u, s, vt = np.linalg.svd(X, 0)
  File "/usr/local/lib/python2.7/dist-packages/numpy/linalg/linalg.py", line 1359, in svd
    u, s, vt = gufunc(a, signature=signature, extobj=extobj)
  File "/usr/local/lib/python2.7/dist-packages/numpy/linalg/linalg.py", line 99, in _raise_linalgerror_svd_nonconvergence
    raise LinAlgError("SVD did not converge")
numpy.linalg.linalg.LinAlgError: SVD did not converge
</code></pre>

<p>Everything else such as SQL connection and charts seem to be working fine.</p>

<p>I found the exact error point in the data but still I am unsure why this is happening. If I run the dates from '2006-12-31' to '2016-09-20', I get the error. However if I run the program from '2007-01-01' to '2016-09-20' the program completes without error. </p>

<p>Below is a snipping of the data.</p>

<p>GBPUSD</p>

<pre><code>TIME, BID-OPEN, BID-HIGH, BID-LOW, BID-CLOSE, ASK-OPEN, ASK-HIGH, ASK-LOW, ASK-CLOSE, VOLUME

'2007-01-03 22:00:00', '1.9513', '1.9531', '1.9397', '1.9428', '1.9511', '1.9529', '1.9395', '1.9426', '11882'
'2007-01-02 22:00:00', '1.9736', '1.975', '1.9481', '1.9513', '1.9734', '1.9748', '1.9479', '1.9511', '12260'
'2007-01-01 22:00:00', '1.9581', '1.9741', '1.958', '1.9736', '1.9579', '1.9739', '1.9578', '1.9734', '8113'
'2006-12-31 22:00:00', '1.958', '1.9581', '1.958', '1.9581', '1.9583', '1.9583', '1.9579', '1.9579', '1'
'2006-12-28 22:00:00', '1.9631', '1.967', '1.9566', '1.958', '1.9634', '1.9673', '1.9569', '1.9583', '9684'
'2006-12-27 22:00:00', '1.9561', '1.9678', '1.9554', '1.9631', '1.9564', '1.9681', '1.9557', '1.9634', '10025'
'2006-12-26 22:00:00', '1.9536', '1.9633', '1.9527', '1.9561', '1.9539', '1.9636', '1.953', '1.9564', '10049'
</code></pre>

<p>EURUSD</p>

<pre><code>TIME, BID-OPEN, BID-HIGH, BID-LOW, BID-CLOSE, ASK-OPEN, ASK-HIGH, ASK-LOW, ASK-CLOSE, VOLUME

'2007-01-03 22:00:00', '1.31701', '1.31791', '1.30761', '1.30831', '1.31679', '1.31769', '1.30739', '1.30809', '10377'
'2007-01-02 22:00:00', '1.32731', '1.32911', '1.31461', '1.31701', '1.32709', '1.32889', '1.31439', '1.31679', '9935'
'2007-01-01 22:00:00', '1.32021', '1.32971', '1.32001', '1.32731', '1.32029', '1.32949', '1.31979', '1.32709', '6711'
'2006-12-28 22:00:00', '1.31491', '1.32051', '1.31381', '1.32021', '1.31499', '1.32059', '1.31389', '1.32029', '8010'
'2006-12-27 22:00:00', '1.31131', '1.32021', '1.31061', '1.31491', '1.31139', '1.32029', '1.31069', '1.31499', '8313'
'2006-12-26 22:00:00', '1.30971', '1.31781', '1.30951', '1.31131', '1.30979', '1.31789', '1.30959', '1.31139', '8242'
</code></pre>

<p>How do I fix this error message, please could someone explain to me what the issue is?</p>

<p>Thank you</p>

<p>After some testing with other data, my first thought was the quantity of rows being tested. This was not the case. </p>

<p>The problem was a missing data point which can be seen from the snipping above. Row '2006-12-31 22:00:00' was missing from the 'EUR-USD' sample causing the calculation to NaN, the calculation could only utilise one complete side 'GBP-USD'. Hope that makes sense.</p>

<p>Problem solved by inserting a new row, albeit a fictitious one using the SQL code below.</p>

<pre><code>INSERT INTO `EUR-USD`.`tbl_EUR-USD_1-Day` (`TIME`, `BID-OPEN`, `BID-HIGH`, `BID-LOW`, `BID-CLOSE`, `ASK-OPEN`, `ASK-HIGH`, `ASK-LOW`, `ASK-CLOSE`, `VOLUME`)
VALUES ('2006-12-31 22:00:00', '1.32021', '1.32971', '1.32001', '1.32731', '1.32029', '1.32949', '1.31979', '1.32709', '6711');
</code></pre>

<p>Moving forward I will write a script to spot missing data and calculate an average value of before and after the missing data point.</p>

<p>UPDATE</p>

<p>After further reading I suggest not updating the records in the database, it is best to keep the RAW data 'as is' and manipulate it with Pandas.</p>

<p><a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow">Pandas</a> has some excellent options below is just one of them. </p>

<p>FFILL is a crude way of filling in missing data. I will be testing other methods and update this post accordingly.</p>

<pre><code>idx = pd.date_range('1997-12-31 22:00:00', '2016-09-18 22:00:00', freq= 'D')

EURUSD = EURUSD.reindex(idx, method='ffill')
GBPUSD = GBPUSD.reindex(idx, method='ffill')
</code></pre>

python ubuntu pandas quantitative-finance