How to display P&L account data in Excel Pepperstone

How to track my Pepperstone P&L in Excel

Having a live view of your acount data is really important (and useful) when you are spending more and more time trading in Excel!

Here is a quick guide on setting up a live Excel view of your account data, such as profile & loss (P&L), account balance and margin.

This post assumes you have set up your account number in a named range in my other post here, if not head over and finish those steps first.


Copy the following names and cell formulas into your Excel sheet.

Currency:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "currency")

Balance:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "balance")

Equity:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "equity")

Floating PL:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "pl")

Floating PL %

=RTD("fxbluelabs.excelrtd",,AccountNumber,"pl") / RTD("fxbluelabs.excelrtd",,AccountNumber,"balance")

Used Margin:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "usedmargin")

Free Margin:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "freemargin")

Margin Use %:

=RTD("fxbluelabs.excelrtd",,AccountNumber,"usedmargin") / RTD("fxbluelabs.excelrtd",,AccountNumber,"equity")

Last Update:

=RTD("fxbluelabs.excelrtd",,AccountNumber, "LastUpdateTime")

And that’s it!
Take a look at our downloads section for working examples of live P&L data in Excel.

How to connect Pepperstone Excel RTD

Test Pepperstone Excel connectivity

Install Metatrader and Excel RTD expert adviser. These tools are available from your Pepperstone client area.

Metatrader 4 will need a matching Pepperstone Excel RTD Expert Advisor.
Similarly, Metatrader 5 has an associated RTD Expert Advisor


Setup

Open Metatrader and double click on the Pepperstone-Excel RTD expert advisor in your Navigator window.

Pepperstone Excel RTD Advisor setup
Metatrader 4 Excel RTD Expert Advisor

The Pepperstone Excel RTD expert advisor window pops up, ensure that Allow DLL Imports is checked under the Common tab and press OK

Check Allow DLL Imports

When you see the Excel RTD link window, your Excel connectivity is up and running.
Ensure Accept Commands is checked, this allows your trading sheet to issue commands to MetaTrader
Take note of your Account ID as you will need this in Excel later on

Excel RTD link

Test your Excel Connectivity

Open Excel and create a tab named Account

Pepperstone Excel Account

On the top of the sheet, type your Pepperstone Account Number into a cell and name the cell AccountNumber, highlighted below in red

Name cell for Pepperstone Account
use a named cell for your account number

Using a named cell is important, as many of the RTD functions need your account number to work

To test your connectivity, let’s query your account balance in a cell. Paste the following cell formula into an empty cell on your sheet:

=RTD("fxbluelabs.excelrtd",,AccountNumber,"balance")

Note how we use the AccountNumber named range to pass your account number to Pepperstone. If all is working as expected, you should see a live account balance.

To retrieve more account information, take a look at our post here on tracking P&L in Excel with Pepperstone.

Account and P&L info from Pepperstone RTD
How to add close cancel button excel trading pepperstone

Add a cancel/close all button using Pepperstone

One of the first pieces of functionality I like to add to a demo trading sheet, is a cancel/close all button. I normally make it big and easy to press!

This button will cancel all pending orders and close out open positions. This is very useful for demo accounts and testing your trading system as a quick way to reset the sheet. Remember that your P&L will naturally change when you close out your positions.

To achieve this with Pepperstone, you will need to dive into some code.

 


This post assumes you have set up Metatrader with the Pepperstone Excel RTD Expert Advisor. If not, go through this post first and come back when you’re done.

If you don’t have the Developer options available in Excel yet, go through my other post here and come back when you’re done.

Add a button to your sheet using the Developer Tab->Insert->Button
and click on your sheet to place the button

Add a button to Excel trading sheet
Adding a button through the Developer Options

Resize your new button using the corner size controls.

Cancel button in Excel trading sheet
resize the button

Right-click on the button and select Properties.
Set a name for the button, choose a background colour, font and foreground colour for the text.

set the name, background and foreground settings

Right-click on the button and select View Code

The VBA Editor will open with your new code macro

Cancel orders with Excel Pepperstone VBA

Paste the following code into your new function

Set cmd = CreateObject("FXBlueLabs.ExcelCommand")
    res = cmd.SendCommand(Range("AccountNumber").Value, "CLOSEALL", "", 20)
    ' use this to view the result:  Sheets("Trading").Range("AA5").Value = FormatDateTime(Now, 4) & " - " & res

Test your button

Ensure that you are not in Design mode using the Developer menu. Design mode will look selected like this:

Excel VBA design mode trading Pepperstone
Design mode in Developer menu

If you need to, click on Design mode to turn it off.

You are now ready to test out your big red button!