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!

Throttle RTD Interval

How to throttle the RTD interval

By default, Microsoft Excel will throttle RTD updates into your spreadsheet. This means that data can be delayed, especially if you are expecting multiple updates per second.

To check your current RTD throttle interval, follow these steps:

  • Open the Visual Basic editor using Alt + F11
  • Open the Immediate Window using View->Immediate Window or Ctrl + G
  • Type into the immediate window, “?Application.RTD.ThrottleInterval” (without the quotes) and press Enter

The default value for Excel is 2000 milliseconds or 2 seconds, which means that updates will not be refreshed within 2 seconds of each-other in your sheet.



How to change the RTD interval

You can set a new value for the RTD throttle interval in the same immediate window as above, using the following syntax:

Application.RTD.ThrottleInterval=1000

which would set the throttle to 1 second.

To remove the throttle completely, and have Excel send updates as quickly as it can to your spreadsheet, you can turn it off with

Application.RTD.ThrottleInterval=0

About RTD

What is RTD and DDE in Excel?

DDE

Dynamic Data Exchange (DDE) is a Microsoft technology for transferring information between applications and is the older of the two technologies. DDE utilises Windows messaging to send and receive data.

RTD

Real-time Data (RTD) is a newer technology introduced specifically for Microsoft Excel for high-speed data transfer, making it an ideal choice for financial data applications. RTD utilises a combined push and pull technology to allow for fast updates of data into an Excel sheet.

Install 64 bit Excel

How to install 64 bit Excel

By default, Microsoft recommends and installs the 32 bit version of Office (and Excel). The main reason, I believe, is for support with the many Excel plugins that are supported on 32 bit Excel.

Here are some of the reasons you may be interested in 64 bit Excel:

  • You have very large datasets -> Excel 64 bit can access more memory and is better with big data
  • You need to support files over 2gb in size -> Excel 64 bit will not be restricted like the 32 bit version
  • You are using a plugin that requires or recommends the 64 bit version

Download the 64 bit version

Before you click ‘download’, ensure that you have selected the 64 bit version. If there is no 32 bit or 64 bit, it usually means you are downloading the 32 bit version.

Office 365

For Office 365, click on Language and Install Options->Additional Install options and select 64 bit Excel

Office Professional Plus 2016 and 2019

If you are installing from a downloaded version of Office, running Setup will usually install the 32 bit version.

Open the Office folder and run Setup64.exe to install 64 bit Office