![\"\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/pyxll-logo-with-text-300x110.png\"){kind=logo}
<\/a><\/figure>\n\n\n\nIf you’ve been working with large data sets then you will already be using Python! Python is the most widely used programming language for data science, machine learning, and industries that use those such as Finance and Engineering.<\/p>\n\n\n\n
The PyXLL Excel add-in bridges your Python code and Excel, enabling you to build custom tools to use directly in Excel or deploy to Excel users in your team (even if they have no knowledge of Python).<\/p>\n\n\n\n
With PyXLL, Excel becomes a user interface toolkit for you to build out solutions in Excel. The Python code runs locally and can import all of your Python packages. You can write Python functions to be called from the workbook, in exactly the same way as Excel’s built-in functions. Because the Python code still lives outside of Excel, you can reuse the same Python code across Excel, batch processing, web apps, and everywhere else you use that code.<\/p>\n\n\n\n
To get started using PyXLL, please visit https:\/\/www.pyxll.com<\/a>.<\/p>\n\n\n\n If you’re new to PyXLL, this video is a great place to start https:\/\/www.pyxll.com\/docs\/videos\/worksheet-functions.html<\/a>.<\/p>\n\n\n\n ArcticDB is a scalable, high performance DataFrame database developed by Man Group. It powers research and trading activity not just within Man Group but across a growing number of organizations including some of the world’s largest financial firms like Bloomberg to name just one.<\/p>\n\n\n\n One of the key features of ArcticDB is how easy it is to get up and running. Traditionally, setting up a large database requires a lot of effort and coordination with database teams, but with ArcticDB you choose where you want to store you data (even keeping it locally on your PC) and start saving pandas DataFrames to it.<\/p>\n\n\n\n To use ArcticDB in your Python environment, pip install it using the following command<\/p>\n\n\n\n Please visit https:\/\/arcticdb.io<\/a> for more details about ArcticDB and help getting started. ArcticDB is developed by Man Group, https:\/\/www.man.com<\/a>.<\/p>\n\n\n\n To look at how we can leverage large data sets in Excel, we first need a large data set! For this example, we’re going to use a free foreign exchange intra-day tick data set from https:\/\/www.histdata.com<\/a>.<\/p>\n\n\n\n Begin by downloading the CSV files from https:\/\/www.histdata.com\/download-free-forex-historical-data\/?\/ascii\/tick-data-quotes\/eurusd\/2023<\/a>. Even one month of this data is too big to be loaded into Excel!<\/p>\n\n\n\n Next, unzip each of the downloaded zip files so you have a folder of CSV files. Now we need some Python code to load each of these CSV files so we can save them to ArcticDB.<\/p>\n\n\n\n To load the CSV files I’ve used another package, polars (https:\/\/pola.rs<\/a>). This is the fastest way I’ve found of reading large CSV files. If you don’t already have polars installed, you can install it by running “pip install polars”. My CSV files were located in a folder named “E:\/fx-data” so you will need to change the code below to where you’ve saved the files.<\/p>\n\n\n The code above loads all of the fx data into a pandas DataFrame, “dfp”. Next we can create an ArcticDB library and save that data. Once the data is saved in ArcticDB it can be queried and retrieved quickly.<\/p>\n\n\n That one line at the end, Let’s start with something simple; reading a small section of the data for a specific interval.<\/p>\n\n\n\n We first need to get the ArcticDB library object, which I’m going to write as a function since we will use that quite often:<\/p>\n\n\n Once we have the library we can access the data using the “read” method. We’ll do that in a second function which we’ll call “raw_tick_data”.<\/p>\n\n\n You’ll see why I have written the above as two functions very shortly… but for now, we can call the first to get the library, and the second to get the data for a specific interval as follows:<\/p>\n\n\n And with that simple few lines of code we’ve not got a DataFrame containing the raw tick data for a specific time interval!<\/p>\n\n\n\n We’ve seen above how simple and fast it is to query the raw tick data for a time interval using just a little bit of Python code. Let’s face it though, it would be much more convenient if you could pull the same data up in Excel! Especially if you work as part of a wider team where many people live and breath in Excel, having tools like this at their disposal is invaluable.<\/p>\n\n\n\n To expose those two functions to Excel all we need is to install the PyXLL Excel add-in, configure the add-in to load the module we wrote above, and add the “@xl_func” decorator to the functions. The @xl_func decorator tells the PyXLL add-in we would like to call those functions in Excel.<\/p>\n\n\n The parameter to right of each @xl_func tells the PyXLL add-in how to convert the Excel input arguments to Python, and how to convert the Python return value to an Excel type. Other than that, you can see the code is exactly the same as before.<\/p>\n\n\n\n With the Python module saved, PyXLL configured to load it, and Excel started, we can call “=adb_library” from an Excel workbook and the ArcticDB Library object is returned to the sheet.<\/p>\n\n\n\n We can then pass that Library object to our “raw_tick_data” function, along with the other options for the ticker, start and end date, and load the data we need directly in Excel – no Python experience required!<\/p>\n\n\n\n We now have an amazingly quick and simple way to access huge data sets directly in Excel, pulling in only the data needed so the sheet stays small and responsive.<\/p>\n\n\n\n Querying the raw tick data is great if we’re drilling in to a very specific time period. Other times, we want to see how the market is behaving at a higher level. By ‘resampling’ the tick data we can calculate the open, high, low and close price for regular intervals. This collapses the fine grained tick data into something much more comprehensible.<\/p>\n\n\n\n To resample our tick data into 15 minute intervals, for each 15 minute interval we record the price at the start of the interval (open); the maximum price in that interval (high); lowest price in the interval (low) and the last price in the interval (close).<\/p>\n\n\n\n ArcticDB makes resampling data in this way simple, and fast! Letting ArcticDB take care of the resampling is much more efficient than loading all of the data and doing it in Python. Once we expose that functionality to Excel the result is a super-fast tool for building interactive spreadsheets.<\/p>\n\n\n\n We’ll write a function as before to query the ArcticDB library. This time we use the QueryBuilder API to build a query that fetches the data and does the resampling in one go:<\/p>\n\n\n I’ve included the @xl_func decorator on this function as with the functions above. This is what makes this function callable from Excel. With this function added to the Python module, either reload the PyXLL add-in or restart Excel and the function will be available.<\/p>\n\n\n\n There’s a more detailed example of using resampling in the ArcticDB examples here https:\/\/docs.arcticdb.io\/dev\/notebooks\/ArcticDB_demo_resample<\/a>.<\/p>\n\n\n\n Querying and resampling data using this function in Excel is incredibly fast!<\/p>\n\n\n\n Now we’ve learned how to query and resample data we can look at ways to visualize that data. A common chart type used in finance is the “candlestick” chart. It’s exactly what we need to show the open\/high\/low\/close (OHLC) resampled data we’ve computed above.<\/p>\n\n\n\n Our final function queries and resamples the data as before. But, rather than return the data to be displayed in the Excel grid, we will plot the data as a chart and display that chart in Excel.<\/p>\n\n\n\n To do this we will use two more packages, “mplfinance” and “matplotlib”. Matplotlib is a well known Python plotting library and can be used to create virtually any type of plot. To make things simple, mplfinance provides some convenience functions specific to finance, such as plotting candlestick charts.<\/p>\n\n\n The PyXLL function “plot” takes the matplotlib figure and displays it in Excel, just below where the function is called. The PyXLL plot function also works with other Python plotting libraries so you can create any visualizations you need beyond the basic Excel charts, using Python.<\/p>\n\n\n\n You might have noticed the addition of the “mav” argument to the function above. This is a list of “moving averages”. The mplfinance library can draw the moving averages for us. This extra argument allows the Excel user to pass in a list of periods so they can control which moving averages get drawn.<\/p>\n\n\n\n To read more about the plotting capabilities of PyXLL, see this page in the PyXLL docs https:\/\/www.pyxll.com\/docs\/userguide\/plotting\/index.html<\/a>.<\/p>\n\n\n\n In this post we’ve seen how to save and query huge data sets into the ArcticDB database. ArcticDB greatly simplifies organizing your financial tick data, or indeed any time series data you might be dealing with!<\/p>\n\n\n\n Using PyXLL, we can write tools to be used in Excel, written in Python. This allows us to take advantage of packages like ArcticDB from within Excel. By using Excel as a front-end to our Python code we massively reduce the time needed to develop interactive tools. Rather than a web app with the obligatory “Export to Excel” button, or overnight reports generating CSV files, we can have the tools we need right in Excel.<\/p>\n\n\n\n All of the code and an example workbook used in this blog post can be downloaded from here https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/arcticdb-fx-demo.zip<\/a><\/p>\n\n\n\nIntroducing ArcticDB<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/ArcticDBCropped-300x218.png\")
<\/a><\/figure>\n\n\n\npip install arcticdb<\/code><\/pre>\n\n\n\n23 Millions rows in Excel, using PyXLL and ArcticDB<\/h2>\n\n\n\n
Preparing the data<\/h3>\n\n\n\n
\nimport polars as pl\n\n# Load the csv data using polars\ndf = pl.scan_csv("E:\/fx-data\/DAT_ASCII_EURUSD_T_*.csv", has_header=False)\n\n# Extract time, bid, ask and mid (mid is calculated from bid and ask)\ndf = df.select(\n pl.col("column_1").alias("timestamp").str.to_datetime("%Y%m%d %H%M%S%3f", time_unit="ns"),\n pl.col("column_2").alias("bid"),\n pl.col("column_3").alias("ask"),\n (pl.col("column_2") + ((pl.col("column_3") - pl.col("column_2")) \/ 2)).alias("mid"),\n ).sort("timestamp", descending=False)\n\n# Convert to pandas and ensure index is sorted\ndfp = df.collect().to_pandas()\ndfp = dfp.set_index("timestamp")\ndfp = dfp.sort_index()\n<\/pre><\/div>\n\n\nLoading the data into ArcticDB<\/h3>\n\n\n\n
\nimport arcticdb as adb\n\n# Create the ArcticDB library on our local file system\nac = adb.Arctic("lmdb:\/\/E:\/fx-data\/arcticdb")\nlib = ac.get_library("fx-test", create_if_missing=True)\n\n# Write the DataFrame created above to the ArcticDB library\nlib.write("EURUSD", dfp)\n<\/pre><\/div>\n\n\nlib.write(\"EURUSD\", dfp)<\/code>, is all that’s needed to save our DataFrame to ArcticDB! Next we will look at how to read it back out.<\/p>\n\n\n\nQuerying the data from ArcticDB<\/h3>\n\n\n\n
\ndef adb_library(url, library_name):\n ac = adb.Arctic(url)\n return ac[library_name]\n<\/pre><\/div>\n\n\n
\ndef raw_tick_data(lib, symbol, start, end):\n data = lib.read(symbol, date_range=[start, end])\n return data.data\n<\/pre><\/div>\n\n\n
\nimport datetime as dt\n\nlib = adb_library("lmdb:\/\/E:\/fx-data\/arcticdb", "fx-data")\n\nstart = dt.datetime(2023, 8, 3, 7, 0, 0)\nend = dt.datetime(2023, 8, 3, 7, 1, 0)\n\ndata = raw_tick_data(lib, "EURUSD", start, end)\n<\/pre><\/div>\n\n\nBringing ArcticDB into Excel with PyXLL<\/h3>\n\n\n\n
\nimport arcticdb as adb\nfrom pyxll import xl_func\n\n@xl_func("str, str: object")\ndef adb_library(url, library_name):\n ac = adb.Arctic(url)\n return ac[library_name]\n\n@xl_func("object, str, datetime, datetime, int: dataframe<index=True>")\ndef raw_tick_data(lib, symbol, start, end):\n data = lib.read(symbol, date_range=[start, end])\n return data.data\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-154023.png\")
<\/a><\/figure>\n\n\n\n![\"\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-154304.png\")
<\/a><\/figure>\n\n\n\nMaking sense of tick data using resampling<\/h3>\n\n\n\n
\n@xl_func("object, str, datetime, datetime, str, int: dataframe<index=True>")\ndef resampled_tick_data(lib, symbol, start, end, freq):\n qb = adb.QueryBuilder()\n\n qb = qb.resample(freq, closed='right').agg({\n 'high': ('mid', 'max'),\n 'low': ('mid', 'min'),\n 'open': ('mid', 'first'),\n 'close': ('mid', 'last')\n })\n\n data = lib.read(symbol,\n date_range=[start, end],\n query_builder=qb)\n\n df = data.data.dropna()\n return df\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-165204.png\")
<\/a><\/figure>\n\n\n\nPlotting a candlestick chart in Excel<\/h3>\n\n\n\n
\nimport matplotlib.pyplot as plt\nimport mplfinance as mpf\nimport pyxll\n\n@xl_func("object, str, datetime, datetime, str, int[], str: var")\ndef plot_fx_rates(lib, symbol, start, end, freq="1D", mav=[], style="seaborn-v0_8"):\n # Build the query that will get the resampled data\n qb = adb.QueryBuilder()\n qb = qb.resample(freq, closed='right').agg({\n 'high': ('mid', 'max'),\n 'low': ('mid', 'min'),\n 'open': ('mid', 'first'),\n 'close': ('mid', 'last')\n })\n\n # Read the data from our ArcticDB library\n df = lib.read(symbol,\n date_range=[start, end],\n query_builder=qb).data\n\n # Plot the candlestick chart using pyplot and mplfinance\n with plt.style.context(style):\n # Get a matplotlib Figure and Axes object to plot the candlestick chart on\n fig, ax = plt.subplots()\n fig.suptitle(symbol)\n ax.grid(True)\n\n # Use matplotlibfinance to draw the candlestick chart\n mpf.plot(df,\n type='candle',\n mav=[x for x in mav if x],\n ax=ax)\n\n fig.tight_layout()\n\n # Show the chart in Excel\n pyxll.plot(fig)\n\n return fig\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-19-174118-1024x985.png\")
<\/a><\/figure>\n\n\n\nWrapping Up<\/h2>\n\n\n\n
Resources<\/h2>\n\n\n\n
\n