Loading...

RTD & Market Depth

Disclaimer:  The information on this page makes references to commercially available software (Microsoft Excel™ ) for the purposes of educating our clients on the functionality of our software.  Aerarius Indicium does not endorse, or make any recommendations as to the use of, any specific software other than its own PhoenixAI software.

Please note also that although the market symbol BHP is used in the examples on this page, this is for illustration purposes only and should not be taken as advice to buy or sell anything.


Market depth data via RTD:

Using RTD, you can view 10 levels of bid and ask orders for market depth.  The data is summary depth only, and can not show individual orders.

DDE does not support the MARKET_DEPTH data type.

This may seem like quite a complex section, and it is.  But it's also not particularly difficult, so I urge you to read it carefully, and implement it if you feel confident enough to try.


The RTD code for market depth is:

=RTD("phoenixai.quotes","","MARKET_DEPTH","BHP.ASX")

This code, when entered in Excel, produces information like this:

{45.59,7007,45.6,8740;45.58,14477,45.61,7287;45.57,2075,45.62,1021;45.56,3585,45.63, 2038;45.55,1517,45.64,3515;45.54,5280,45.65,23668;45.53,11000,45.68,8000;45.51,789
,45.69,2686;45.5,6408,45.7,11520;45.48,500,45.71,4724}


Not very useful, is it?

That's because the values are being presented in an array.

We can make it useful by wrapping the RTD request inside another function inside Excel.

This function is not built-in though, so we have to add it into our spreadsheet.


To do this, open the spreadsheet you want to use, or just create a fresh spreadsheet.

Press ALT+F11 to open the Visual Basic editor for the workbook.

Right-click on the workbook as shown below

Click Insert > Module

A module window will appear

The code for the function is as follows:

Function GetDepth(data As String) As Variant
    GetDepth = Evaluate(data)
End Function

Copy & paste the code (exactly as it is) into the window for the module.  You will see that the function name (on the right) changes to GetDepth automatically.

Close the Visual Basic editor.

Save the Workbook.

Note that the GETDEPTH module only needs to be done once to new spreadsheets in which you want to use the MARKET_DEPTH RTD call.  Once it's been added in, it'll stay there unless you delete it.


The GETDEPTH function takes the information provided by RTD, and places it in an array, which is more easily readable.

The code used to construct the array is:

=GETDEPTH(RTD("phoenixai.quotes","","MARKET_DEPTH","BHP.ASX"))

As you can see, when calling the GETDEPTH function, the RTD request is simply placed inside parentheses ().

We can make this even more useful by including a cell reference instead of specifying a symbol.

=GETDEPTH(RTD("phoenixai.quotes","","MARKET_DEPTH",INDIRECT(ADDRESS(1,2))))

In this case the cell we're referencing is B1, so if we type "BHP" (without the quotes) into the cell B1, the array would return the first 10 depth levels for BHP.  You can, of course, change the cell reference to suit yourself.

Row,column notation is used to specify the cell reference (ADDRESS), and the INDIRECT function is included to ensure that the specified cell is referenced regardless of where the array is placed or moved in the spreadsheet.

Row,column notation simply means the cell reference is specified by row first, then column.  So cell B2 would be 2,2 and cell D6 would be 6,4 (row 6, column 4).

INDIRECT is sort of like using the $ to specify an absolute cell reference,but unlike using $, the reference won't change if columns or rows are added or removed from the spreadsheet.

INDIRECT also has other uses which we're not going into here - Google is your friend.


Now, how to go about building an array?

It's actually pretty easy.

First, get the formula you're going to use:

=GETDEPTH(RTD("phoenixai.quotes","","MARKET_DEPTH",INDIRECT(ADDRESS(1,2))))


Change the cell reference to suit yourself; we're going to make it C2 (row 2, column 3)  just to be different.

=GETDEPTH(RTD("phoenixai.quotes","","MARKET_DEPTH",INDIRECT(ADDRESS(2,3))))

Then copy the edited formula (CTRL+C).


In your spreadsheet, go to the cell your formula references (in this case, C2)

Enter the symbol for the array (in this case, bhp)

Now click on the cell where you want to start the array.  This cell will become the top-left corner of a 4x10 grid (4 columns X 10 rows).


We'll start our array on C4.

Click on the cell C4, and drag to cell F13.  This should highlight all cells from C4 to F13 - a 4x10 grid.

Press F2 to open the first cell for editing

Press CTRL+V to paste the formula.

Press CTRL+SHIFT+ENTER to complete the array.

If you just ended up with a value in one cell, try again and check your steps as you go.

If you've done it right, you should end up with something that looks like this:

bhp      
       
45.97
45.96
45.95
45.94
45.93
45.92
45.91
45.9
45.88
45.86
1116
9028
11788
13968
6024
3436
3270
2196
1432
3851
45.98
45.99
46
46.01
46.02
46.03
46.04
46.05
46.06
46.07
3368
11954
99587
12406
6208
2531
2990
1577
627
112


Now you can pretty it up a bit so it's easy to see what's going on, by adding some column headers for "bid" and "ask", etc.

 

BHP
Bids Asks
45.97
45.96
45.95
45.94
45.93
45.92
45.91
45.9
45.88
45.86
1116
9028
11788
13968
6024
3436
3270
2196
1432
3851
45.98
45.99
46
46.01
46.02
46.03
46.04
46.05
46.06
46.07
3368
11954
99587
12406
6208
2531
2990
1577
627
112

 

Note that had you used the formula that didn't have a cell reference, but just specified the symbol:

=GETDEPTH(RTD("phoenixai.quotes","","MARKET_DEPTH","BHP.ASX"))

It would still have worked fine, and you would be able to move the array anywhere in your spreadsheet.  but part of the flexibility of using a spreadsheet is being able to change what's being shown, which is why we extended the example to include the use of cell references and the INDIRECT function.

=GETDEPTH(RTD("phoenixai.quotes","","MARKET_DEPTH",INDIRECT(ADDRESS(2,3))))


Multiple instances of depth in a spreadsheet.

Below is an example of three depth arrays showing different symbols.

You can have up to six of these depth arrays operating in your spreadsheet at the same time, which is why it's a good idea to use cell references in the formula.

 

 

Related Topics:
Watch Lists
Market Depth
DDE & RTD Data Exchange

 

Microsoft, Excel and Microsoft Excel are registered trademarks of Microsoft Corporation.

45.97
45.96
45.95
45.94
45.93
45.92
45.91
45.9
45.88
45.86