Predictions Trading Visualisations

Building a macro-sentiment indicator with Python


The idea of a macro-sentiment indicator is to monitor macro data and sentiment data at the same time.

The idea is straightforward. Divergence between macro and sentiment data can generate a signal.

For example, good macro data and bad sentiment would generate a buy signal.

Bad macro data and good sentiment data generates a sell signal.

I published an Amazon book about 3 years ago. Unfortunately, the code relies on Quandl which doesn’t support most of the queries anymore.

Since I have been looking at the Python bokeh package, this is a good opportunity to put it to use.

Below you can see a first graph, that shows the DAX (German composite index) on top. Below, in the left column you can see three Macro time series that have some predictive ability on stock behavior:

(a) ECB Interest Rate

(b) Inflation

(c) USD/EUR exchange rate.

In the right column there are three sentiment indicators:

(a) CBOE Put / Call Ratio

(b) VIX

(c) Number of 52-week highs on the NYSE

The next step is to consolidate these data into indicators and then back test.

Output and code below.


## macro sentiment indicator in python (simplified)
import pandas as pd
import pandas_datareader as pdr
from bokeh.plotting import figure, output_file, show
from bokeh.layouts import row, column
from import gridplot
import numpy as np
from datetime import datetime, date
import urllib.request, urllib.error, urllib.parse
from pandas import DataFrame, Index
from pandas.tseries import offsets 
import numpy as np
import pandas as pd
import quandl
import datetime

def getPutCallRatio():
    """ download current Put/Call ratio"""
    urlStr = ''

        data = urllib.request.urlopen(urlStr)
    except Exception as e:
        s = "Failed to download:n{0}".format(e);
    headerLine = 2
    return pd.read_csv(data,header=headerLine,index_col=0,parse_dates=True)

def getHistoricData(symbols =  ['VIX','VXV','VXMT','VVIX']):
    ''' get historic data from CBOE
        return dataframe
    if not isinstance(symbols,list):
        symbols = [symbols]
    urls = {'VIX':'',
    startLines = {'VIX':1,'VXV':2,'VXMT':2,'VVIX':1}
    cols = {'VIX':'VIX Close','VXV':'CLOSE','VXMT':'Close','VVIX':'VVIX'}
    data = {}
    for symbol in symbols:
        urlStr = urls[symbol]
        print('Downloading %s from %s' % (symbol,urlStr))
        data[symbol] = pd.read_csv(urllib.request.urlopen(urlStr), header=startLines[symbol],index_col=0,parse_dates=True)[cols[symbol]]
    return pd.DataFrame(data)

# get marko data
start = datetime.datetime(2000, 1, 1)
dax = pdr.DataReader("^GDAXI", "yahoo", start)
dax = dax[['Close']]
dax.columns = ['DAX_Close']    

# interest rate data ECB
df_ecb = pd.read_csv("C:/Users/dro.use.f129p70/Downloads/data.csv",
                     skiprows = [0,1,3,4] 

df_ecb.index = df_ecb[df_ecb.columns[0]]
df_ecb.index = pd.to_datetime(df_ecb.index, format='%Y-%m-%d')
df_ecb = df_ecb.drop(df_ecb.columns[0:11], axis = 1)
df_ecb = df_ecb.drop(df_ecb.columns[1], axis = 1)
df_ecb.columns = ["ECB_interest_rate"]

## inflation data
df_infl = pd.read_csv('C:/Users/dro.use.f129p70/Downloads/HICP.csv', sep=",", skiprows=[0,1,2,3])
df_infl.index = pd.to_datetime(df_infl.iloc[:,0], format ="%Y%b")
df_infl = df_infl.iloc[:,1]
df_infl = df_infl.resample('D').mean().ffill()

## Sentiment Data
##put call
putcall = getPutCallRatio()
putcall = putcall[['P/C Ratio']]

## VIX 
vix = pdr.DataReader("^VIX", "yahoo",start = datetime.datetime(2000, 1, 1))
vix = vix[['Close']]
vix.columns = ['Close.vix']

#new 52 nyse
new_highs = quandl.get("URC/NYSE_52W_HI", authtoken="jKD-Z9WsXtk1K7UR9D-J")

#df_infl = df_infl.resample('D')

# EUR / USD Dollar Exchange rate
eur_usd = pdr.DataReader("EURUSD=X", "yahoo", start)
eur_usd = eur_usd[['Close']]
eur_usd.columns = ['Close_USD/EUR']

## Create marko indicator

#Plot all macro data together with DAX
# Put all makro data into single data frame incl dax

df_full = pd.concat([dax, df_ecb, df_infl, eur_usd, putcall, vix, new_highs], axis=1)
df_full = df_full.ffill()
df_full = df_full.loc['2006 Nov':,]

## plot
p1 = figure(title = 'DAX', 
           x_axis_label = 'Time', 
           y_axis_label = 'Price', 
           height = 300,
           width = 1100

p2 = figure(title = 'Interest rate ECB', 
           x_axis_label = 'Time', 
           y_axis_label = 'Marginal lend. facility', 
           height = 200,
           width = 550 )

p3 = figure(title = 'Inflation Europe', 
           x_axis_label = 'Time', 
           y_axis_label = 'Inflation', 
           height = 200,
           width = 550)

p4 = figure(title = 'EUR/USD', 
           x_axis_label = 'Time', 
           y_axis_label = 'EUR/USD', 
           height = 200,
           width = 550)

p5 = figure(title = 'CBOE Put Call Ratio', 
           x_axis_label = 'Time', 
           y_axis_label = 'P/C Ratio', 
           height = 200,
           width = 550)

p6 = figure(title = 'VIX', 
           x_axis_label = 'Time', 
           y_axis_label = 'VIX', 
           height = 200,
           width = 550)

p7 = figure(title = 'New highs NYSE', 
           x_axis_label = 'Time', 
           y_axis_label = 'New highs', 
           height = 200,
           width = 550)

p1.line(x=df_full.index, y=df_full.DAX_Close, color = 'blue', legend = 'DAX')
p1.legend.location = 'top_left'

p2.line(x=df_full.index, y=df_full.ECB_interest_rate, color = 'gray', legend = 'ECB interest rate')

p3.line(x=df_full.index, y=df_full['[Percentage change ]'], color = 'red', legend = 'Inflation')
p3.legend.location = 'bottom_left'

p4.line(x=df_full.index, y=df_full['Close_USD/EUR'], color = 'green', legend = 'EUR/USD')

p5.line(x=df_full.index, y=df_full['P/C Ratio'], color = 'orange', legend = 'P/C Ratio')

p6.line(x=df_full.index, y=df_full['Close.vix'], color = 'black', legend = 'VIX')

p7.line(x=df_full.index, y=df_full['Numbers of Stocks'], color = 'gray', legend = 'New 52w highs')

#layout = column(p1,p2,p3,p4)

layout = gridplot([p1],


The two functions to download CBOE data are from

You may also like
Dynamic charts with googleVis
Financial situation of municipalities in North Rhine-Westphalia, Germany

Leave Your Comment

Your Comment*

Your Name*
Your Webpage