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

Is my Excel 64 bit or 32 bit?

The easiest way to tell which bitness your Excel is running, is to open Task Manager on Windows using Ctrl+Shift+Esc or to use Start->Task Manager.

Pro Tip: Despite which version is installed, it is a good idea to check in Task Manager which version is running as this will affect plugins you are trying to install and use

Excel 32 bit using Task Manager (Windows 10)

If you see a * or 32 bit next to the application or process name, the process is running as 32 bit.

If there is no 32 bit or * next to the application or process name the process is running as 64 bit.

Excel 64 bit using Task Manager (Windows 10)

Excel 2013

You can check for 32 bit vs 64 bit using File->Account and clicking on About Excel.


Excel 2016

Similar to Excel 2013, you can check for 32 bit vs 64 bit using File->Account and clicking on About Excel.


Excel 2019

Excel 2019 and 2016 look nearly identical, with the same way of checking 32 bit vs 64 bit through File->Account and clicking on About Excel.

Excel 365

Office 365 and 2019 are fairly similar, both in looks and functionality. You can check 32 bit vs 64 bit in the same was using File->Account and clicking on About Excel.

Which version of Excel am I running?

Excel 2003

Excel 2007

Excel 2010

Excel 2013

Excel 2016

Excel 2019

Excel 365


Excel 2003:

Excel 2003 had a file menu and toolbar that looked like this:

You can click on Help->About Microsoft Office Excel


The product version is at the top of the About Microsoft Excel dialog.


Excel 2007:

Excel 2007 introduced the Office logo replacement for the File Menu, which looks like this:

Click on the Office Logo->Excel Options then click on Resources on the left panel. This will show you the specific Excel 2007 version information.


Excel 2010:

2010 re-introduced the File menu, which looked like this:

Click on File->Help and take a look on the right of the screen for version and build info.


Excel 2013:

Excel 2013 had a lighter menu with a File menu like this:

Click on File->Account

and select About Excel


Excel 2016

Excel 2016 introduced the green menu-bar, which looks like this:

Click on File->Account and select About Excel


Excel 2019

Excel 2019 continued the green toolbar theme, which looks like this:

Click on File->Account and select About Excel


Excel 365

Not sure if you have Office 365 or Excel 2019? Clicking on File->Account will show you:

Click on About Excel to see product information and bitness (64 or 32 bit)

Excel Developer Toolbar

How to add VBA developer options in Excel ribbon

Let’s face it, buttons are cool.

As a VBA Trader, at some point you need to add a button to your trading sheet to pull trades, or to start and stop your auto trader system.

By default, Excel hides the developer options which allows you to add controls and edit code. Here are the steps to add it to your toolbar:

  • Click on File->Options
  • Select Customize Ribbon
  • On the right-hand list, check Developer in the list
  • Press OK
Excel 2019 Developer Options in the Toolbar