ExcelPlotter.py

import sys
import os
import re
import shutil
import math
import openpyxl
import numpy as np
import matplotlib
from pyhocon import ConfigFactory
from pyhocon.config_tree import ConfigTree
matplotlib.use('pdf')
import matplotlib.pyplot as plt


def strip_margin(text: str) -> str:
    return re.sub('\n[ \t]*\|', '\n', text)


def get_rounded_min_max_values(min_max: tuple) -> tuple:
    min_val = None

    if min_max[0] < 0:
        min_val = math.floor(min_max[0] + min_max[0] * 0.2)
    else:
        min_val = math.floor(min_max[0] - min_max[0] * 0.2)

    max_val = None

    if min_max[1] > 0:
        max_val = math.floor(min_max[1] + min_max[1] * 0.2)
    else:
        max_val = math.floor(min_max[1] - min_max[1] * 0.2)

    min_val_rounded = get_next_power_of_ten(min_val)
    max_val_rounded = get_next_power_of_ten(max_val)

    return (int(min_val_rounded), int(max_val_rounded))


def draw_hline(axes: plt.Axes, min_val: int, max_val: int):
    axes.axhline(y=0.5, xmin=min_val, xmax=max_val, linewidth=2, color='black')
    axes.annotate(0, (0, 0.31), ha='center', va='bottom')


def get_spike_steps(price_range: int) -> tuple:
    if price_range < 16000:
        small_tick_step = 1000
        big_tick_step = 3000
    elif price_range >= 16000 and price_range < 24000:
        small_tick_step = 1000
        big_tick_step = 6000
    elif price_range >= 24000 and price_range < 30000:
        small_tick_step = 2000
        big_tick_step = 8000
    else:
        small_tick_step = 4000
        big_tick_step = 10000

    return (small_tick_step, big_tick_step)


def get_next_power_of_ten(number: float) -> int:
    factor = 1
    div = abs(number) // 10
    tip = 0

    while div != 0:
        factor *= 10
        tip = div
        div //= 10

    next_power_of_ten = 10 if tip == 0 else (tip + 1) * factor
    return (number / abs(number)) * next_power_of_ten


def draw_vspikes(axes: plt.Axes, min_val: int, max_val: int):
    price_range = abs(max_val - min_val)
    (small_tick_step, big_tick_step) = get_spike_steps(price_range)

    # Big ticks
    for i in range(min_val, max_val, big_tick_step):
        axes.axvline(i, ymin=0.5, ymax=0.8, linewidth=1, color='black')
        axes.annotate(i, (i, 0.31), ha='center', va='bottom')

    # Small ticks
    for i in range(min_val, max_val, small_tick_step):
        axes.axvline(i, ymin=0.6, ymax=0.7, linewidth=1, color='black')


def draw_values(axes: plt.Axes, bond_data: tuple):
    axes.plot(bond_data[0], 0.5, 'bs', zorder=99)
    axes.plot(bond_data[1], 0.5, 'bD', zorder=99)
    axes.plot(bond_data[2], 0.5, 'bs', zorder=99)


class Template:
    def __init__(self, config: ConfigTree):
        self.id = config.get_string('id')
        self.name = config.get_string('name', self.id)

        self.worksheet = {
            'start_index': config.get_int('worksheet.start_index'),
            'anchors': {
                'middle': config.get_string('worksheet.anchors.middle'),
                'low': config.get_string('worksheet.anchors.low'),
                'high': config.get_string('worksheet.anchors.high'),
                'plot': config.get_string('worksheet.anchors.plot')
            }
        }

        self.plot_dimensions = {
            'height': config.get_float('plot_dimensions.height'),
            'width': config.get_float('plot_dimensions.width')
        }

        self.tmp_dir = \
            config.get_string('tmp_dir', f'{self.id}_tmp_plots')

    def __str__(self):
        return strip_margin(f'''Template {self.id}
        | id: {self.id}
        | name: {self.name}
        | worksheet {{
        |   start_index: {self.worksheet['start_index']}
        |   anchors: {{
        |       middle: {self.worksheet['anchors']['middle']}
        |       low: {self.worksheet['anchors']['low']}
        |       high: {self.worksheet['anchors']['high']}
        |       plot: {self.worksheet['anchors']['plot']}
        |   }}
        |   plot_dimensions: {{
        |       height: {self.plot_dimensions['height']}
        |       width: {self.plot_dimensions['width']}
        |   }}
        |   tmp_dir: {self.tmp_dir}
        | }}
        ''')

    def __repr__(self):
        return self.__str__()


class Config:
    def __init__(self, filename: str):
        conf = ConfigFactory.parse_file(filename)

        template_confs = conf.get_list('templates')
        self.templates = \
            dict(map((lambda tc: (tc.id, Template(tc))), template_confs))

        mapping_confs = conf.get_list('mappings')
        self.mappings = dict(map(
            lambda mc: (mc.get_string('template'), mc.get_list('files')),
            mapping_confs))

        # print('Loaded the following templates')
        # [print(t) for _, t in self.templates.items()]
        # print('Loaded the following mappings')
        # [print(f'{t} - {fs}') for t, fs in self.mappings.items()]


class WorkbookProcessor:
    def __init__(self, filename: str, template: Template):
        self.template = template
        self.filename = filename
        self.wb = openpyxl.load_workbook(filename)
        self.ws = self.wb.worksheets[0]
        self.curr_index = template.worksheet.get('start_index')

    def get_next_bond(self) -> tuple:
        worksheet_conf = self.template.worksheet
        price_low = \
            self.ws[f'{worksheet_conf["anchors"]["low"]}{self.curr_index}']
        price_high = \
            self.ws[f'{worksheet_conf["anchors"]["high"]}{self.curr_index}']
        price_middle = \
            self.ws[f'{worksheet_conf["anchors"]["middle"]}{self.curr_index}']

        self.curr_index += 1

        if price_low is not None and price_high is not None:
            return (price_low.value, price_middle.value, price_high.value)

    def plot(self, bond_data: tuple):
        template = self.template
        plot_dimensions = template.plot_dimensions
        fig = plt.figure()
        fig.set_figheight(plot_dimensions['height'])
        fig.set_figwidth(plot_dimensions['width'])
        cur_axes = fig.gca()
        cur_axes.set_ylim(0.3, 0.6)

        (min_val, max_val) = \
            get_rounded_min_max_values((bond_data[0], bond_data[2]))

        draw_hline(cur_axes, min_val, max_val)
        draw_vspikes(cur_axes, min_val, max_val)
        draw_values(cur_axes, bond_data)
        plt.axis('off')

        fig_name = \
            f'{template.tmp_dir}/plot_{self.curr_index - 1}.png'
        plt.savefig(fig_name)

    def update_excel(self):
        from openpyxl.drawing.image import Image
        template = self.template
        img = Image(f'{template.tmp_dir}/plot_{self.curr_index - 1}.png')
        img_name = \
            f'{template.worksheet["anchors"]["plot"]}{self.curr_index - 1}'
        self.ws.add_image(img, img_name)

    def cleanup(self):
        shutil.rmtree(f'{self.template.tmp_dir}')

    def process(self):
        print(f'Processing {self.filename} with {self.template.name} template')

        if not os.path.exists(self.template.tmp_dir):
            os.mkdir(self.template.tmp_dir)

        bond_data = self.get_next_bond()
        while bond_data[0] is not None and bond_data[2] is not None:
            self.plot(bond_data)
            self.update_excel()
            bond_data = self.get_next_bond()
        else:
            print(f'Done processing {self.filename}')

        self.wb.save(self.filename)
        self.wb.close()
        self.cleanup()


def main():
    conf = None

    if len(sys.argv) == 1:
            conf = Config('plotter.conf')
    elif len(sys.argv) == 3:
        if sys.argv[1] == '-c':
            conf = Config(sys.argv[2])
        else:
            help()
    else:
        help()

    for template_name, files in conf.mappings.items():
        template = conf.templates.get(template_name, None)

        if template is not None:
            for filename in files:
                processor = WorkbookProcessor(filename, template)
                processor.process()


def help():
    print(strip_margin('''
    |Invalid usage. Try
    |    plotter.py [-c <configuration file> ('plotter.conf' by default)]
    '''))
    exit()

if __name__ == '__main__':
    main()

plotter.conf

templates = [
    {
        id = CDS
        # name - equals to `id` if not set
        worksheet = {
            start_index = 10
            anchors = {
                middle = K
                low = M
                high = N
                plot = Q
            }
        }
        plot_dimensions = {
            height = 0.58
            width = 4.2
        }
        # tmp_dir - equals to `{id}_tmp_plots` if not set
    },
    {
        id = 'DTGF'
        # name - equals to `id` if not set
        worksheet = {
            start_index = 10
            anchors = {
                middle = 'P'
                low = 'S'
                high = 'R'
                plot = 'X'
            }
        }
        plot_dimensions = {
            height = 0.58
            width = 4.2
        }
    },    
    {
        id = 'DTGL'
        # name - equals to `id` if not set
        worksheet = {
            start_index = 10
            anchors = {
                middle = 'P'
                low = 'S'
                high = 'R'
                plot = 'X'
            }
        }
        plot_dimensions = {
            height = 0.58
            width = 4.2
        }
    },    
    {
        id = 'FXO'
        # name - equals to `id` if not set
        worksheet = {
            start_index = 13
            anchors = {
                middle = 'N'
                low = 'O'
                high = 'P'
                plot = 'S'
            }
        }
        plot_dimensions = {
            height = 0.58
            width = 4.2
        }
    },    
    {
        id = 'WMGP'
        # name - equals to `id` if not set
        worksheet = {
            start_index = 10
            anchors = {
                middle = 'K'
                low = 'P'
                high = 'O'
                plot = 'U'
            }
        }
        plot_dimensions = {
            height = 0.58
            width = 4.2
        }
    },    
    {
        id = 'ZMGP'
        # name - equals to `id` if not set
        worksheet = {
            start_index = 10
            anchors = {
                middle = 'I'
                low = 'O'
                high = 'P'
                plot = 'S'
            }
        }
        plot_dimensions = {
            height = 0.58
            width = 4.2
        }
    }
]

mappings = [
    {
        template = CDS
        files = [ CDS_MGP_Report_20180829.xlsx ]
    },
    {
        template = DTGF
        files = [ DTG_MGP_Report_F_20180515.xlsx ]
    },
    {
        template = DTGL
        files = [ DTG_MGP_Report_L_20180515.xlsx ]
    },
    {
        template = FXO
        files = [ FXOptionen_MGP_Report_20180824.xlsx ]
    },
    {
        template = WMGP
        files = [ Wertpapiere_MGP_Template_v2.xlsm ]
    },
    {
        template = ZMGP
        files = [ Zinsswap_MGP_Report_20180718.xlsx ]
    }
]