Interactive Pandas dataframes with ITables 2.0

 ITables is a Python package available on Github (maintained by Marc Wouts) which changes how Pandas and Polars dataframes are rendered in Python notebooks and other Python applications. It works out of the box without any dependencies in Jupyter, Dash, Streamlit and Marimo. 

For more info see:

• Make your Pandas or Polars DataFrames Interactive with ITables 2.0
• ITables Quick Start

Enable and disable Data Wranger in VS Code for Pandas DataFrame and Series

To disable default rendering of Pandas DataFrames in VS Code Jupyter Notebook with Data Wrangler (after installing the extension) - follow these steps:

1. Open Command Palette : Preferences: Open Settings (UI)
2. Search for: Data Wrangler
3. Uncheck the Data Wrangler>Output Renderer: Enabled Types for Pandas Dataframe and Series to disable the rendering and check them again when needed

Related links:

• Getting started with Data Wrangler in VS Code

Divididend analysis of Telenor ASA using Jupyter Notebook

Cross posted from Divididend analysis of Telenor ASA using Jupyter Notebook

I just published the notebook dividends.ipynb on my GitHub repository jorisp/tradingnotebooks which shows how dividends contribute to the total return. This notebook uses the yfinance API to retrieve the data.  I used Telenor ASA (a Norwegian telecom operator) as an example.

If you are considering to invest in foreign dividend stocks as a Belgian investor, you need to keep in mind the double taxation of dividends. Even with a withholding tax applied abroad, the Belgian government will tax your dividend again at a flat rate of 30%.

Disclaimer: The information on this blog is intended solely for informational and educational purposes. I am not a certified financial advisor, and the content provided here does not constitute professional financial advice. (Full disclaimer)

Getting financial data in Python using the OpenBB SDK

The OpenBB SDK (also known as OpenBB Platform) is developed as open-source (the code is available on https://github.com/OpenBB-finance/OpenBB) by the company Open BB. The OpenBB SDK provides programmatic access to a wide range of financial data sources from one place in a standard way.

The OpenBB SDK was developed to drive the OpenBB Workspace (See Introducing the new OpenBB Terminal ) which provides a customizable platform for financial analysts, investors and researchers that rivals traditional financial terminals without the steep costs.

By default, the OpenBB SDKwill attempt to download data from free sources such as Yahoo Finance but OpenBB SDK integrates with multiple other data sources as well such as , Alpha Vantage, FRED,FMP,SEC,etc .... In most OpenBB API platform calls, you can indicate a different data source - some of them free others requiring a separate subscription - allowing you to pull equities, options, crypto, forex and macroeconomic data using a single SDK. 

Since you can access both historical and real-time market data, OpenBB is ideal for backtesting and live trading strategies. The SDK is compatible with Jupyter Notebooks, Python scripts, and automated trading systems. I recently tested the OpenBB SDK as an alternative to Pandas_DataReader in Jupyter Notebooks, and it worked flawlessly.

I shared this Jupyter notebook on my Github repo:

https://github.com/jorisp/tradingnotebooks/blob/master/openbbdemo.ipynb

Please note that many of the code samples found in various articles and posts are no longer functional due to significant changes in the codebase. The shared Jupyter notebook has been tested with OpenBB 4.3.5 and Python 3.12.8.

Looking at historical returns of stocks and bonds with Power BI and Python

You might already have seen below graph taken from a study by JP Morgan Asset Management, but what if you would like to look at historical returns without going  through the hassle of having to collect all the data yourself?

There is an interesting Excel sheet shared by Aswath Damodaran (@AswathDamodaran)  that you can download from Historical Returns on Stocks, Bonds and Bills: 1928-2022 which looks at returns of different asset classes (stocks, bonds, bills, real estate and gold) over a longer time period.

In this post I will share some tips on how you can use this data in Power BI, Python and Jupyter notebooks. 

This Historical Returns on Stocks, Bonds and Bills: 1928-2023 - Excel file  file is updated in the first two weeks of every year and it is being maintained by Aswath Damodaran, who is a professor of Finance at the Stern School of Business at NYU, he is also known as the "Dean of Valuation" due to his experience in this area.

Visualizing S&P 500 and US Treasury bond returns using Power BI

I first converted the Excel from  xls to xlsx format and afterwards it is quite easy to  import the data from an Excel workbook files in Power BI . It is quite easy to visualize the returns of  both stocks and US treasury bonds using a clustered column chart - I also added a minimum line for both stock and bond returns.

Expected risk and expected return should go hand in hand: the higher the expected return, the higher the expected risk. Risk means means that the future actual return may vary from the expected return (and the ultimate risk is loosing all of your assets). The first visual showed a 20-year annualized return between 1999 and 2018 for the S&P 500 of 5.8%.  Average returns hide however the big swings in yearly returns - e.g. in 2008 (the Great Financial Crisis), the S&P 500 had a -36.5% yearly return. Bonds on average have a lower return but also have a lower risk profile. 

The basic rule of thumb is to keep your “safe money” (i.e., money you don’t want to risk in stocks) in high-quality bonds. While this doesn’t give you 100% protection against losses at all times, it does provide you some peace of mind. I really like this quote: "If you can't sleep at night because of your stock market position, then you have gone too far. If this is the case, then sell your positions down to the sleeping level. (Jesse Livermoore)"

As you can see in the visualization below, in most years with a negative return for the S&P 500, the return for bonds is positive - with two notable exceptions 1969 and 2022. A common saying is to have your age in bonds. Using that general rule, a 45-year-old might have 45% of the total portfolio in bonds. If you want to more aggressive, you would have less than your age in bonds. The last decade with interest rates very low (or even negative) this probably wasn't a very profitable asset allocation but 

things might have shifted.

The US Treasury Bond used in the Excel file is the 10-year US treasury bond for which you can download the data from FRED . The yearly return has been calculated by taking the yield and the price change for a par bond with that specific yield.

You can download the Power BI file histreturns.pbix from my Power BI Github repo

In the long run (see example below for different rolling windows from a 1-year to a  20 year period)  stocks will outperform bonds but this again works with averages and it ignores the tail risk which might wreak havoc in your portfolio.

Reading data from Excel using Python

Now let's take a look at how you can read and manipulate the data in this Excel sheet using Python. To read an excel file as a DataFrame, I will use the pandas read_excel() method. Internally, Pandas. .read_excel uses a library called xlrd which you also need to install but I used the  openpyxl library as an alternative which also works. So before you can read an excel file in pandas, you will need to install 

the openpyxl library. 

The above code reads only the table with data from the Excel file (which I downloaded in a subdirectory data from the Jupyter notebook) - see  pandas.read_excel in the Pandas referencel documentation for full details:

• sheet_name: can be an integer (for the index of a worksheet in an Excel file, default to 0, the first sheet) or the name of the worksheet you want to load
• nrows: number of rows to read
• skiprows: number of rows to skip
• usecols: by default all columns will be read but also possible to pass in a list of columns to read into the dataframe like in the example

I just started exploring some data around stock-bond correlations and will be updating the Juypyter notebook on Github - https://github.com/jorisp/tradingnotebooks/blob/master/HistoricalReturns.ipynb

A couple of weeks ago I noticed this interesting tweet on rolling one-year-stock-bond correlations for six regimes from @WifeyAlpha - I think it would make an interesting exercise to see how to rebuild this using Python.

References:

• Pandas – read_excel() – How to read Excel file in python
• Musings on markets: a return to teaching - the Spring 2023 Edition
• Guide to the Markets | JP Morgan Asset Management

Related posts:

• Interactive chart visualizations using Python and bqplot: visualizing S&P500 returns
• Using the yFinance Python package to download financial data from Yahoo Finance - part 2
• Using the yFinance Python package to download financial data from Yahoo Finance
• Using Python and Pandas Datareader to retrieve financial data - part 2: Fama & French data library
• Using Python and Pandas Datareader to retrieve financial data - part 1: Federal Reserve Data (FRED)

Interactive chart visualizations using Python and bqplot: visualizing S&P500 returns

A couple of months ago, I stumbled upon this interesting presentation Jupyter Notebooks: interactive visualization approaches. The presentation showed how you can use bqplot to build interactive visualizations. 

Bqplot contains a set of 2D plotting widgets built on top of the ipywidgets framework for Jupyter notebooks. The bqplot package aims to bring d3.js visualizations to Python while retaining the flexibility and ease of use of ipywidgets and was developed by the quantitative research team at Bloomberg. You can install bqplot using conda or pip. 

One of the examples built by the team that you can find on Github is a Jupyter notebook which shows US equity market performance (using the S&P 500 index) where you can select an interval on a time series chart - for the selected area you get the total return as well as a histogram of the daily returns.

References:

• Interactive data visualization using Bqplot
• bqplot - Interactive plotting in Jupyter notebook
• bqplot: bringing more interactivity to data

Quick tip: troubleshooting Jupyter notebook not starting correctly

The other day my Jupyter notebooks did not start correctly from Anaconda navigator - luckily the Jupyter docs have a section  Jupyter - What to do when things go wrong. So I tried starting it from Anaconda prompt and it indeed gave me an exception that there was an invalid path in the Jupyter config file - to find out where to look for the config file check out Jupyter common directories and file locations

Using the yFinance Python package to download financial data from Yahoo Finance - part 2

In a previous post I showed how you can download ticker data from Yahoo Finance using the yFinance Python package.  I now updated the  Jupyter notebook code sample using YFinance to also show how you can retrieve additional information (sector, industry, trailing and forward earnings per share, etc...). The Ticker class in the yFinance library contains the info property which returns a dictionary object ( a collection of key-value pairs where each key is associated with a value) which allows you to access specific information about an asset.

Since I wanted to know how fast data retrieval would be I also include the %%time magic command . Wall clock time measures how much time has passed. CPU time is how many (milli)seconds the CPU was busy.

Yahoo Finance contains data about stocks, Exchange Traded Funds  (ETF), mutual funds and stock market indices - the information that you can retrieve for each of these differs, so it is safe to check in your code for the quoteType. Below example retrieves information about Apple stock, the iShares MSCI AWCI UCITS ETF (Acc) and a thematic mutual fund from KBC.

I also included a code snippet which shows how to retrieve this information for multiple assets and convert this into a Pandas dataframe.

Using the yFinance Python package to download financial data from Yahoo Finance

In a previous post I explained how you can retrieve data from Yahoo Finance using Python and Pandas Datareader - an alternative Python library for retrieving data from Yahoo Finance is yFinance maintained by Ran Aroussi. 

If you are using conda package manager, you will notice that you can not install yfinance using conda so you will need to revert to pip install yfinance. All documentation is available on yFinance  as well as on https://github.com/ranaroussi/yfinance but I also uploaded a  Jupyter notebook code sample on my Github - https://github.com/jorisp/tradingnotebooks/blob/master/yfinance_sample.ipynb

Using Python and Pandas Datareader to retrieve financial data - part 3: Yahoo Finance

Yahoo Finance is one of the most popular sources of free financial data. It does not only contain historical data but also financial statements, dividend information and calculated metrics like e.g. 50 and 200 day moving average, beta, etc ... Yahoo Finance does not have an officially supported API anymore but pandas-datareader  still allows you to access the data from Yahoo Finance in Python (other alternatives are yfinance and yahoo_fin).

This post is part of a series on using Pandas datareader to retrieve financial data:

• Using Python and Pandas Datareader to retrieve financial data - part 1: Federal Reserve Data (FRED)
• Using Python and Pandas Datareader to retrieve financial data - part 2: Fama & French library
• Using Python and Pandas Datareader to retrieve financial data - part 3: Yahoo Finance

In this post I have used version 0.10.0 of pandas-datareader  (released July 13, 2021) which is currently working with Yahoo Finance - previous versions of pandas-datareader had to be updated after Yahoo made some changes on the underlying API.

Warning: Accessing Yahoo Finance using Python libraries is quite brittle so don't try to built production trading systems using this data source.

Accessing the Yahoo Finance API using pandas-datareader is very simple as shown in the screenshot below but I would also recommend implementing a cache mechanism for your queries using the requests-cache Python library to avoid having your IP address being banned. The full source of this Jupyter notebook is available at https://github.com/jorisp/tradingnotebooks/blob/master/YahooFinancesingle.ipynb

References:

• Yahoo Finance API - A complete guide
• yfinance 
• yahoo_fin

Using Python and Pandas Datareader to retrieve financial data - part 2: Fama & French data library

This post is part of a series on using Pandas datareader to retrieve financial data:

• Using Python and Pandas Datareader to retrieve financial data - part 1: Federal Reserve Data (FRED)
• Using Python and Pandas Datareader to retrieve financial data - part 2: Fama & French library
• Using Python and Pandas Datareader to retrieve financial data - part 3: Yahoo Finance

In this post we will look at the datasets made available by Eugene Fama and Kenneth French. Eugene Fama and Kenneth French did a lot of research on which factors drive security returns. In 1993, they published the Three Factor Model (see article "Common risk factors in returns of stocks and bonds", Journal of Financial Economics 33, 1993), which showed that their factors (size of the firm, book-to-market values and excess return) capture a statistically significant fraction of the variation of stock returns. In 2014, Fama and French adapted their model to include five factors.  Fama won the Nobel Prize for Economics in 2013 for his research. Fama also published a number of papers on the Efficient Market Hypothesis and random walk theory.

Fama and French still publish the returns of various investment factors analyzed by them on their homepage on a regular basis.  You can download this data using the pandas_datareader library - you can take a look at the official documentation,  Fama-French Data (Ken French's Data library) to get started or take a look at the Jupyter notebook that I shared on Github https://github.com/jorisp/tradingnotebooks/blob/master/FAMA.ipynb

References:

• Fama/French forum ideas and observations from Fama and French
• How to use the Fama French model
• Fama-French 5-factor model: five major concerns
• In pursuit of the perfect portfolio: Eugene F. Fama (YouTube - MIT Laboratory for Financial Engineering )

Alternatives to Azure Notebooks preview

Azure Notebooks Preview will be retired on January 15th, 2021 and all user data will be destroyed - don't forget to download your user data before then.  Microsoft alternatives to use are listed on Quickstart: Export a Jupyter Notebook project in Azure Notebooks Preview 

Palm oil price vs SIPEF stock price in Jupyter notebooks with Quandl

Quandl also offers free commodity price data for almost 100 commodities ( See API for commodity data) so I decided to create a Jupyter notebook comparing SIPEF's stock price with the price of palm oil. Take a look at the full notebook I shared on github if you want to learn why - https://github.com/jorisp/tradingnotebooks/blob/master/Quandl_API_Euronext_SIPEFPalmOil.ipynb 

 

Remarks:

• Be careful when embedding images in a Jupyter notebook that you want to publish on Github (see Images in Markdown not showing when uploaded to Git for a discussion on this) - filenames are case sensitive - I got it working using the url syntax
• To store the quandl key I used the approach outlined on how to use secrets in Jupyter Notebooks with python-dotenv
• I really like Matplotlib and its versatility in rendering capabilities  in this notebook I used the ability to visualize multiple time series (see code preview below) with different axes. (see code below)

sipcolor = 'tab:red'
fig,ax1 = plt.subplots()

ax1.plot(data['SIP'],color=sipcolor)
ax1.set_ylabel('SIPEF',color=sipcolor)
#Instantiate a second axes that shares the same x-axis
ax2 = ax1.twinx()
ax2.plot(data['PPOIL'])
ax2.set_ylabel('PALM OIL USD')
plt.gcf().set_size_inches(15,8)
plt.show()

Related posts:

• Working with multiple time series trading data from Quandl in Jupyter Notebooks
• Using Euronext  stock data from Quandl in Jupyter Notebooks

Quick tip: workaround kernelspec module not installed with jupyter notebooks in Visual Studio Code

This weekend I tried out working with Jupyter Notebooks in Visual Studio Code. Since I already have Jupyter up and running as part of the Anaconda distro I thought this would be easy but it took me longer than expected. 

First step is to install the Python extension for VS Code  and then you can start working with existing Jupyter Notebooks by just double clicking an existing notebook. Unfortunately this immediately threw an exception "Error: Jupyter cannot be started. Error attempting to locate jupyter: 'Kernelspec' module not installed in the selected interpreter." The workaround suggested on StackOverflow Python/Jupyter notebook in VSCode does not use the right environment did not resolve the issue and also the fixes described on Github - New version tries to install kernelspec were not working.

The workaround which seems to be working for me is starting VS Code from the Anaconda Prompt in the Jupyter workspace directory by typing "code ."

How to use secrets in Jupyter Notebooks with python-dotenv

It is not a good idea to embed the the secrets (credentials, keys, etc..) that you need in your Python code directly in your Jupyter Notebooks. You can store these secrets in environment variables but an alternative is using the python-dotenv library. Python-dotenv is a library that reads key-value pairs from  a .env file and adds them to environment variables.

To get started you need to install the latest version of python-dotevn and then you will need to create a .env file in the same folder as your .ipynb files.

Tip: You may not be able to create .env file directly in Windows Explorer - use the command prompt and the echo command to create this file.

You can read a key-value pair in just one line of code after you loaded the extension and the os module which provides functions to interact with the operating system.

Source code is available on https://github.com/jorisp/tradingnotebooks/blob/master/dotenv.ipynb

References:

• How to not embed credentials in Jupyter notebooks

Quick tip: reload Python modules in Jupyter with the autoreload magic method

If you are writing your own Python modules and you include them in your Jupyter notebooks you might notice that by default your code updates in the custom Python module are not reflected once the module has been load by Jupyter. You can fix this by adding %autoreload at the top of your notebook.

%load_ext autoreload
%autoreload 2

For the documentation take a look https://ipython.readthedocs.io/en/stable/config/extensions/autoreload.html

Quick tip: change Jupyter notebook startup folder in Anaconda

This excellent walk through explains how to change the Jupyter notebook startup folder in Anaconda - quick summary of the steps:

1. Generate config file by running jupyter notebook --generate-config in the Anaconda prompt
2. Open the c:\Users\\.jupyter\jupyter_notebook_config.py
3. Uncomment c.NotebookApp.notebook_dir = '' and fill in your startup folder e.g. 'c:\jupyter_workspace'  

For an overview of all of the configuration setting in the jupyter_notebook_config.py file take a look at Config file and command line options (Jupyter documentation)

Running the Jupyter stock trading notebooks in Azure Notebooks

Update October 2020:  Azure Notebooks Preview will be retired from January 15th, 2021 and all user data will be deleted - for alternatives take a look at https://docs.microsoft.com/en-us/azure/notebooks/quickstart-export-jupyter-notebook-project

In the previous blog posts - Using Euronext stock data from Quandl in Jupyter notebooks and Working with multiple time series trading data from Quandl in Jupyter Notebooks I showed how you can use Jupyter notebooks to analyze stock trading running the Anaconda distribution from my local machine. The notebooks are available on Github - https://github.com/jorisp/tradingnotebooks

It is however also possible to run them in Microsoft Cloud using Azure Notebooks on  https://notebooks.azure.com/ . Azure Notebooks is a free hosted service  to develop and run Jupyter notebooks in the cloud along with supporting packages for Python, R and F#. You can just login and get started without having to setup or install anything and run the code within your browser.

On the home page you will see a number of Jupyter notebook projects which you can clone into your own personal library.

By default, projects run on the Free Compute tier, which is limited to 4GB of memory and 1GB of data. You can bypass these limitations and increase compute power by using a different virtual machine that you've provisioned in an Azure subscription. For more information see Use Azure Data Science Virtual Machines

To be able to run the Jupyter notebook from https://github.com/jorisp/tradingnotebooks/blob/master/Quandl_API_Euronext_ABI_Shared.ipynb on Azure notebooks, the only thing you need to do is installing the Quandl Python module by adding !pip install quandl in a new code cell on the top.

The free compute tier from Azure notebooks is running a custom version of Anaconda but not always the latest version - if you need specific Python modules you need to be aware that the installed version might not always be the latest version

Working with multiple time series trading data from Quandl in Jupyter Notebooks

In the previous example - Using Euronext stock data from Quandl in Jupyter notebooks I downloaded a single dataset from Quandl. But it is also possible to download multiple datasets by passing in a list of Quandl codes.

In the example below, I downloaded the prices of a number of diversified holding companies which are traded on Euronext Brussels and compared the cumulative returns (not including dividend payments) using Jupyter Notebooks.

The Quandl Python API allows you to make a filtered time series call and request only specific
columns - in this example the 'Last' (Closing price) is retrieved by specifying the index 4. In a next
step I renamed the columns in the pandas dataframe to make it easier to work with the data
afterwards.



Take a look at the full python notebook at https://github.com/jorisp/tradingnotebooks/blob/master/Quandl_Belgian_Holdings-Shared.ipynb to see how this data can be used to visualize cumulative returns for these different stocks

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

%matplotlib inline import quandl import matplotlib.pyplot as plt quandl.ApiConfig.api_key = "<Your Key Here>" #Retrieve Last price only for the 5 holdings (excluding mono holdings) trading on Euronext Brussels #Data is available from February 2014 onwards - Ackermans Van Haren (ACKB), Brederode (BREB), Sofina (SOF), #GBL and Bois Sauvage (COMB ) data = quandl.get(['EURONEXT/ACKB.4','EURONEXT/BREB.4','EURONEXT/SOF.4','EURONEXT/GBLB.4','EURONEXT/COMB.4']) #Rename column names data.rename(columns={'EURONEXT/ACKB - Last': 'ACKB', 'EURONEXT/BREB - Last': 'BREB','EURONEXT/SOF - Last':'SOF', 'EURONEXT/GBLB - Last':'GBLB','EURONEXT/COMB - Last':'COMB'},inplace=True)

Using Euronext stock data from Quandl in Jupyter notebooks

The last couple of weeks I have been learning about Python and how to use it for stock and derivative trading. One of the challenges is getting stock trading data for European stocks (without having to pay for it).  One of the first things I started with is using Jupyter notebooks to quickly visualize stock market information.

The easiest way to get started with Jupyter is using an all-in-one Python distribution - the one I used is Anaconda since it is easy to setup and it includes a number of interesting libraries I want to use in next steps.

I like to try out things hands-on but I did use a number of training resources to get up to speed:

• Jupyter Notebook Tutorial: the definitive guide (Datacamp)
• Create and share Analytics with Jupyter Notebooks (Pluralsight) 

To get trading data about European stocks I used Quandl. Quandl is a marketplace for financial and economic data which is either freely available or requires a paid subscription. Data is contributed by multiple data publishers like World Bank, trading exchanges and investment research firms. Quandl provides REST API access to the available data sets but also has specific Python and R libraries. You first need to register to get an API key. A lot of European stocks are traded on Euronext and Quandl provides you access to Euronext data - https://www.quandl.com/data/EURONEXT-Euronext-Stock-Exchange

Install the quandl Python package using the Anaconda command prompt. It is best to setup virtual environments to manage separate package installation that you need for a particular project, isolating the packages in other environments but for simplicity I just installed in the base environment.

Next it is quite easy to retrieve stock data from Quandl - you first import the quandl package and next you call the quandl.get() method. By default, Quandl will retrieve the dataset into a pandas DataFrame. Since I specified no additional parameters, the entire timeseries dataset was retrieved - from February 2014 until now. Afterwards I used the plot command which uses the matplotlib library to display a graph of the closing prices.

For the full Jupyter notebook take a look at Github https://github.com/jorisp/tradingnotebooks/blob/master/Quandl_API_Euronext_ABI_Shared.ipynb