Category:

Basic Data Analysis

Native Excel Functionality with Python and openpyxl Module

by s666 9 July 2018

In this article I shall be looking into using the Python module “openpyxl” to manipulate data within the Python ecosystem, while also being able to tap into excel functionality directly. I believe Python is a much better ecosystem within which to do any kind of data munging/analysis, however Excel is a much used platform, favoured by many as the means of final presentation once the munging/analysis has been completed.

Also, while using Python within a business office environment, users can often come across the situation whereby they need to present their findings/results to their colleagues who often times don’t even have Python or the necessary modules installed.

So how about this – being able to use Python to do the data analysis grunt work, AND being able to use Python to call native Excel methodology including conditional formatting, chart creation/insertion among other things, rather than having to save tabular data to excel/csv and THEN opening Excel and creating charts and including formatting that way.

Sounds good to me!

9 July 2018 2 comments

Basic Data Analysis

Replicating Excel Functionality in Pandas

by s666 30 June 2018

This article is aimed at showing how to replicate some common Excel tasks using Python and the Pandas library. The point and click interface of Excel means the learning curve is somewhat less
steep than it is for using Pandas – however once a certain level of proficiency is met when using Pandas, the possibilities presented are far greater than you would find in Excel (at least that’s what I believe).

I have downloaded some test data from Microsoft in “.xlsx” format which can be downloaded below if you wish to follow along.

Financial Sample Download

Let us first read our data into a Pandas DataFrame and visualise the first 5 rows of data, just to see what we are playing with.

30 June 2018 2 comments

Basic Data Analysis

Trading Strategy Analysis using Python and the FFN Package – Part 2

by s666 15 March 2018

Hi all, this is the second part to the “Trading Strategy Analysis using Python and the FFN Package” post (the first part can be found here).

Last time we went over the use of the “PerformanceStats” object in ffn, whereas this time I want to concentrate on the “GroupStats” object. The former is for use with single series of data, whereas the latter can accept a full DataFrame with multiple columns representing multiple price/equity series simultaneously.

15 March 2018 14 comments

Basic Data Analysis

Trading Strategy Analysis using Python and the FFN Package – Part 1

by s666 27 February 2018

In this post I will be reviewing and running through examples of using the brilliant python module, “ffn – Financial Functions for Python“, which has been created by Philippe Morissette and released on the MIT license. The github page can be found here (http://pmorissette.github.io/ffn/index.html)

The module helps quickly carry out analysis of trading strategies and financial asset price series/history. It can deal with single series using the “PerformanceStats” class, or multiple combined assets simultaneously using the “GroupStats” class.

Let’s begin with the simple, one asset/strategy case and run through some examples.

We begin by importing the relevant modules (and if working in a Jupyter notebook, the call to allow matplotlib objects to be rendered in the browser).

27 February 2018 17 comments

Basic Data Analysis

Stock Clusters Using K-Means Algorithm in Python

by s666 8 February 2018

For this post, I will be creating a script to download pricing data for the S&P 500 stocks, calculate their historic returns and volatility and then proceed to use the K-Means clustering algorithm to divide the stocks into distinct groups based upon said returns and volatilities.

So why would we want to do this you ask? Well dividing stocks into groups with “similar characteristics” can help in portfolio construction to ensure we choose a universe of stocks with sufficient diversification between them.

The concept behind K-Means clustering is explained here far more succinctly than I ever could, so please visit that link for more details on the concept and algorithm

I’ll deal instead with the actual Python code needed to carry out the necessary data collection, manipulation and analysis.

First things first, we need to collect the data – lets run our imports and create a simple data download script that scrapes the web to collect the tickers for all the individual stocks within the S&P 500.

8 February 2018 12 comments

Basic Data Analysis

Investment Portfolio Optimisation with Python

by s666 21 January 2017

In this post I’ll be looking at investment portfolio optimisation with python, the fundamental concept of diversification and the creation of an efficient frontier that can be used by investors to choose specific mixes of assets based on investment goals; that is, the trade off between their desired level of portfolio return vs their desired level of portfolio risk.

21 January 2017 83 comments

Basic Data Analysis

Monte Carlo Simulation in Python – Simulating a Random Walk

by s666 28 November 2016

Monte Carlo Simulation in Python – Simulating a Random Walk

Ok so it’s about that time again – I’ve been thinking what my next post should be about and I have decided to have a quick look at Monte Carlo simulations.

Wikipedia states “Monte Carlo methods (or Monte Carlo experiments) are a broad class of computational algorithms that rely on repeated random sampling to obtain numerical results. Their essential idea is using randomness to solve problems that might be deterministic in principle. They are often used in physical and mathematical problems and are most useful when it is difficult or impossible to use other approaches. Monte Carlo methods are mainly used in three distinct problem classes:[1] optimization, numerical integration, and generating draws from a probability distribution.”

28 November 2016 18 comments

Basic Data Analysis Trading Strategy Backtest

Analysis of Moving Average Crossover Strategy Backtest Returns Using Pandas

by s666 2 September 2016

In this post I thought I’d take advantage of the results we got from the moving average crossover strategy backtest in the last post (can be found here), and spend a bit of time digging a little more deeply into the equity curve and producing a bit of analysis concerning some key performance indicators and some generally (hopefully) interesting data.
For completeness, below is all the code needed to produce the strategy backtest results for our impending analysis, along with charting the equity curve just to make sure we have run it correctly.

2 September 2016 9 comments

Basic Data Analysis

What are ‘skew’ lookin’ at? – calculating skew and kurtosis with Python…

by s666 4 April 2016

Python…skew…kurtosis…

So you have a series of returns you wish to analyse….mean and variance are easy to calculate…how easy does Python make it to calculate skew and kurtosis?

When we look at a series of investment returns, we tend to concentrate on the first 2 ‘moments’ of the distribution; that is the mean and the variance of the returns. The mean gives us a representation of the average expected return, and the variance gives us a measure of the dispersion of returns around the mean. But those two measures don’t give us the full picture. There are ‘higher order’ moments to be aware of…

4 April 2016 4 comments

Basic Data Analysis

Python Pandas…Eats, shoots and leaves…?

by s666 23 March 2016

Ok so I thought it was about time we got into the nitty gritty of some “financial analysis”, rather than have me spout info regarding available beginners courses like I’m some sort of salesman. “I want to do some analysis”, I hear you scream…

Well ok then…let’s start. With the Python Pandas module.

So financial analysis usually starts by asking questions. I mean, our analysis has to be focused on something right? Data/financial analysis is just a tool, rather than an end in itself.

23 March 2016 4 comments