Dashboard for automated trading portfolio dashboard - is this functionality possible?

[Burtoninlondon]

Hello,

I develop automated trading strategies / algorithms which I use to trade equities & futures as part of a trading / investment portfolio.

The trading platform I use to develop and run my strategies unfortunately does not have a portfolio feature therefore I have developed an Excel workbook to provide the stats I need and to visualise my equity curve.

I am considering developing a PowerBI dashboard to replace my excel workbook as I think this will offer me a better way of visualising the performance of my portfolio and also provide me with a way of analysing the impact of adding new automated trading strategies to my portfolio. Before I started I wanted to ask this community if what I am trying to achieve with PowerBI is achievable or if I should find another way to do it.

I have a handful of questions which I’m hoping you may be able to answer. Please be aware that I am a new user of PowerBI so I do not yet have a full awareness of its capabilities or knowledge of the extent and functionality of DAX formulas so please take that into account when reading my questions.

1. The data sets I will use will be trade data (date + time of trade, profit/loss per trade, number of shares, etc.) exported from my trading platform into an excel file. Each automated trading strategy will have its associated trade data on an individual excel file and I currently have 15 automated trading strategies which will mean that the data set will comprise 15 individual excel files. The excel files will be ‘live’ with new trades manually added by me on a periodic basis (each week or month). I want to know if the datasets can be modelled to automatically absorb new data added onto the base excel files following the dashboard creation or if I would need to remodel the links, associations, etc every time I add new data to the excel file?

2. The trade data on the excel files are ordered by date (year, month, day, hour, minute & second) and each individual excel file will have its own range of dates. The dates would not be unique as different automated trading strategies may on occasion place trades at the same time and there would be instances where there are multiple trades on the same day. I believe that to ‘connect’ the individual excel files and to manage the dates I would need to create a master calendar table in the PowerBI model. Is it possible to link the individual excel files to the master calendar or can the date range of the calendar table only can it only be linked to one excel file?

3. Once the dashboard is created and the links are established to the excel files for the 15 automated trading strategies I would want to have the ability to ‘test’ additional trade data from new automated trading strategies against the portfolio to see if the portfolio performs better or worse. Think of this as me adding a new excel data file with new trade data to the model. Is it possible to have a feature in the PowerBI dashboard which would automate the process of adding this data?

I appreciate that I may not have been as clear as I could be in framing my questions but if you can offer any feedback from your experience I would greatly appreciate it.

Many thanks,

Neil

[Sahir_Maharaj · Accepted solution]

Hello @Burtoninlondon,

This project is certainly achievable.

1. Power BI can automatically absorb new data added to your base Excel files. Here’s how you can set it up:

• You can use the Get Data feature to import multiple Excel files into Power BI.
• Set up scheduled refreshes to ensure that any updates to your Excel files (new trades) are automatically (configure this in the Power BI Service)
• When you add new data to your Excel files, as long as the structure (columns) remains consistent, Power BI will refresh the data without needing to remodel the links or associations.

2. Creating a master calendar table in Power BI is a best practice for handling dates. You can use DAX for this- Here is an example:

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE(2000,1,1), DATE(2030,12,31) ),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Day", DAY([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q")
)

3. You can indeed test trade data from new automated trading against your existing portfolio.

• Power BI has built-in What-If parameters that you can use to simulate the impact of adding new trading strategies. You can create scenarios to see how the portfolio performs with the new data.

hope this helps and feel free to reach out should you require any further assistance.