7C Database

7C Database
7C Database

My major project for 2019 was supposed to be the construction of an investment database.

The idea was that some kind of software-supported system would save me time, and better enforce discipline:

  1. A lack of time leads me to skip regular maintenance tasks that I might otherwise perform.
  2. And there are a lot of mechanical analysis chores that could uncover investment opportunities that I would otherwise miss.

Such a database needs to do at least two things:

  1. Monitor and analyse existing holdings.
  2. (Technical) Analysis of prospective holdings.

The original plan was to build a prototype in Microsoft Access, with a medium-term goal of learning Python and moving closer towards mainstream algorithmic programming.

  • The coronavirus lockdown of 2020 provided the trigger to get started with that goal.
Data

The system would need to import and hold data.

  • At a minimum, this would be price data for technical analysis.

Ideally, it would be free, but I could afford a small monthly fee.

  • I’m not planning to start day trading, so all I need is the end of day data – OHLCV (Open, High, Low, Close, Volume).

My safety net plan was to use EODData, who I have used before.

2019 Audit

I began in 2019 with an old (2013) version of the database – I hoped that much of what I want from it was already in there.

  • This audit process was intended to familiarise me with the system in particular and the development environment in general.

And it would cover much of the requirement definition and design processes along the way.

Processes

I looked at the structure of the old system, and then reviewed all the tables, queries and macros.

  • I also looked at the processes (see diagram above) and the tables we’ll need (see diagram below).

It became clear that the 2013 version of the database was focused on my “Portfolio X-Ray” requirements, and that the technical analysis functions had been removed.

  • So I would have to do a similar audit of the previous (2008) version of the database.

Tables

Dashboard

Dashboard pie1 200317

The system will also need to re-create my day-to-day Dashboard spreadsheet:

  • The first tab covers my Accounts (cash, Premium Bonds, a personal debt, company and state pensions, workplace pensions, SIPPs, ISAs, properties, VCTs and spread-betting)
  • The second shows the asset allocations within the accounts which hold listed instruments

Dashboard table 200317

The third has a fairly detailed breakdown of the target asset allocation in the main Passive Portfolio, plus live allocations and variances for these.

  • The final tab ties everything together, showing how the various accounts and portfolios contribute to the top-level asset allocation.

Dashboard pie2 200317

Python

During the Covid lockdown of 2020, I started looking into Python in more detail, and in 2021, I started publishing articles on the relevance of the language to Private Investors.

I introduced Python as an interpreted, high-level, general-purpose programming language (named after the Monty Python comedy group).

  • I provided a road map for the topics that I will be covering in this series of articles.
Pluses of Python
  1. Easy to learn – readable, with simple syntax – so well-suited to part-time programmers and is more readable and less elaborate than C++ or Java.
    • For people like me, this should also lead to greater productivity, especially in the early months/years.
  2. Versatile, because of the availability of a large number of libraries which support most of the necessary financial tasks:
    • data manipulation – reading, writing, cleaning, web-scraping and support for industry-standard databases (eg. SQL)
    • maths, stats, and time-series analysis
    • financial analysis
    • backtesting and simulation
    • machine-learning and automation (including task automation and bots)
    • graphics plotting, including interactive visualizations and web development
    • interfaces to external systems such as email, Excel and Google Sheets
  3. Well- developed eco-system and wide use both in universities (to teach programming in data science) and investment banks and hedge funds (for machine learning and algorithmic trading).
    • There is a constant stream of community material that is either easily accessible or cutting-edge.
    • There are also dozens of books and online courses available to teach you Python – many of which are free.
  4. Python is also free and open-source
Python negatives

The main disadvantage of Python is that is it slow to execute.

  1. Python is a high-level programming language and is interpreted at run-time, rather than being compiled
    • So it’s slower than some other (more difficult languages)
  2. Python has dynamic types (variable types don’t need to be declared)
    • This makes the computer work harder when looking up variables – with a speed impact once more.
  3. But computers are cheap and fast these days – and will only become cheaper and faster – so execution time is no longer crucial.
    • We aren’t aiming even at intraday trading, let alone HFT.
    • As long as our daily analysis can be run in an hour or two, we’ll be fine.
  4. The scarce resource today is programmer time, or in my case, single private investor time.
    • Productivity is much more important than execution speed.
    • At my stage of life, I have only so much time I can spare.

Python has strengths and weaknesses, but for my purposes, the former greatly outweigh the latter.