Business Central, Excel and Python a good combination?

Microsoft introduced Python in Excel a couple of months ago:
Get started with Python in Excel – Microsoft Support
With this feature you can easily create python code inside Microsoft Excel. You can do this by just type ‘=py’ in a shell and you can create your python code:

Ok but what has this feature to do with Business Central?
Well, a couple of releases ago you can export reports to Excel and create in Excel your layout. And therefore I think it is a very good combination if you like Python.

The example

Well I have create a very small example to test this. In this example there is a report that exports all the “Vendor Ledger Entries”. But the end user wants also the “Customer Ledger Entries” And combine those in a graph in the same report.

To handle this situatie you can easily create an Excel report that exports all the “Vendor Ledger Entries”. To add the “Customer Ledger Entries” you can access the API’s for this. In this case it is an API from Business Central but off course you can add also Api’s from a different application.

Example of the “Vendor Ledger Entries” (from Excel reporting):

Example of the “Customer Ledger Entries” (from the API):

If the data is there you can work with python to create for example a dataframe based on both data sheets and sum it up per quarterly:

vendordf = pd.DataFrame(xl("Data[#All]", headers=True))
## Drop Columns
vendordf = vendordf.drop(columns=['OriginalAmount','DocumentDate','VendorName'])
## Rename columns
vendordf = vendordf.rename(columns={'VendorNo': 'SourceNo'})
## Add category
vendordf['Category'] = 'Vendor'
## Change to datetime
vendordf['PostingDate'] = pd.to_datetime(vendordf['PostingDate'])
vendordf['DueDate'] = pd.to_datetime(vendordf['DueDate'])
## Sort
vendordf.sort_values('PostingDate')

vendordf = vendordf.resample('Q', on='PostingDate')['Amount'].sum()

You can do the same as for the customers and you have a sum per quarter of the year:

If you want a graph of it you can add this in another shell:

import matplotlib.pyplot as plt
plt.figure(figsize=(10,6))  
plt.plot(vendordf.index, vendordf, label='Vendor')  
plt.plot(customerdf.index, customerdf, label='Customer')  

And this will plot your graph:

Even better when you download the report from Business Central again all the data will be refreshed and the Python script will executed again.

Conclusion

I think Python in Excel is very usefull if you are familiar with Python. But allot of basic stuff you can handle already with PowerQuery orf Pivor tables etc.
You must be aware that also sometimes your Python script will get an output of ‘None’ don’t why this it. But a day later the code was working again.

But after all I think this is a great feature to work with it if you have complex datasets and with a Python script you can easily do your thing in a dataframe (and I think maybe even faster then PowerQuery).