RTD offers an updated alternative to DDE linking to Excel spreadsheets.

RTD offers the following advantages:

  •  Has Excel function-like syntax
  •  Puts the real-time data in a cell
  •  Can use cell references as part of the formula
  •  Updates in real time, frequency based on user configurations
  •  Is extremely efficient
  • Is able to drive calculation; formulas based on cells that reference real-time data should update accordingly
  • Keeps track of the state and location of real-time data formulas so that the server doesn’t have to

RTD is the system default  – if you want to use DDE linking to a spreadsheet, you must specify how you want to link:

  • Select the Preferences button ( ) or File/Preferences from the Main Menu Bar
  • Click on the General selection
  • Under Format Links as, select DDE
  • Click OK

These preferences are dynamic and can be changed immediately if you want to do other links using the RTD or DDE linking format.

To create an RTD link:

  • Highlight the data in ProphetX
  • Right click and select Format Excel Link
  • Open your spreadsheet and highlight the cell where you want to start copying
  • Right click and select Paste

Notice the cell formula below: It’s telling the spreadsheet to do an RTD link from “Quotes”, and link QCL@1 and the “Last” price (the “prophetx.rtdserver” is built into ProphetX)

Excel6

Setting the  RTD Throttle

RTD updates are user configurable. This allows you to choose how often you want prices in the spreadsheet to update. For example, you might want certain data to only update once per minute. When the update occurs, it will return the most recent data available occurs. The advantage of this is that it saves a large amount of system and network resources.

2 ways to set the RTD controls:

  • In Excel, which specifies how often Excel will check for updates.
  • In the ProphetX registry, which specifies how often the quote server will send updates to Excel.

You can set one or the other or both controls depending on what your linking needs require.

Microsoft Excel: The information below explains the update throttle.

Excel has the notion of a throttle for RTD. By default this throttle is set at 2,000 milliseconds (two seconds). What this means is that Excel only checks to see if it has been notified of an update at most once every two seconds. This can only be modified via the Excel object model or the registry. There is no user interface for configuring the RTD throttle interval in Excel.

  • If the RTD throttle interval is set to -1, this is considered manual mode, and Excel only checks for updates when Excel.Application.RTD.RefreshData is called.
  • If the RTD throttle interval is set to zero, Excel checks for updates every chance it gets.
  • If the RTD throttle interval is set to something greater than zero, Excel waits at least that number of milliseconds between checks for updates.

Caution: If updates come in so frequently that Excel is continuously updating values and doing calculations, Excel might end up in a state where it never gives the user a chance to do anything, effectively getting in a hung state. If this happens, set the Excel throttle interval higher.

 

To set the throttle interval higher through the Excel object model: (Excel 2003)

  • In Excel, go to the Visual Basic Editor (by pressing ALT+F11 or clicking Visual Basic Editor from the Macro menu (Tools menu)).
  • In the Immediate window (press CTRL+G or click Immediate Window on the View menu), type this code:
  • RTD.ThrottleInterval = 1000
  • Make sure your cursor is on the line that you just typed, and then press ENTER.
  • To verify that it is set correctly, type this line of if code in the Immediate window: ? Application.RTD.ThrottleInterval
  • If you put your cursor at the end of this line and press ENTER, it should display 1000. Then you know that your throttle interval is set correctly.

To set the throttle interval higher through the Excel object model: (Excel 2007)

  • If you don’t have the Developer button on your main menu in Excel, click on the Office button in the upper left corner and select Excel Options
  • Click on Popular in the drop-down menu
  • Make sure the “Show Developer tab in the Ribbon check box is checked
    • In Excel, click on the Developer top menu selection
    • Click the Visual Basic button
    • Select View on the Visual Basic main menu bar and select Immediate Window
    • In the Immediate Window, type this code: Application.RTD.ThrottleInterval = 1000
    • Make sure your cursor is on the line that you just typed, and then press ENTER.
    • To verify that it is set correctly, type this line of if code in the Immediate window: ? Application.RTD.ThrottleInterval
    • If you put your cursor at the end of this line and press ENTER, it should display 1000. Then you know that your throttle interval is set correctly.

To set the throttle interval higher through the Excel object model: (Excel 2010)

  • If you don’t have the Developer button on your main menu in Excel, Select File/Options/Customize Ribbon
  • On the right side of the screen, select Developer
    • In Excel, click on the Developer top menu selection
    • Click the Visual Basic button
    • Select View on the Visual Basic main menu bar and select Immediate Window
    • In the Immediate Window type this code: Application.RTD.ThrottleInterval = 1000
    • Make sure your cursor is on the line that you just typed, and then press ENTER.
    • To verify that it is set correctly, type this line of if code in the Immediate window: ? Application.RTD.ThrottleInterval
    • If you put your cursor at the end of this line and press ENTER, it should display 1000. Then you know that your throttle interval is set correctly.

ProphetX Registry:

To set the throttle interval higher through the registry, set the following registry key:

It is a DWORD and is in milliseconds:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options\RTDThrottleInterval (this is the syntax for Excel 2003)

For Excel 2007 enter 12.0

For Excel 2010 enter 14.0

Using Cell References

Cell formulas can be changed to reference specific cells within the spreadsheet.  This is beneficial if you are a heavy spreadsheet user who needs to change prices on the fly without having to continually go back and link additional information in order to get a new price.

For example, in the screen shot above, the formula is referencing the symbol linked from the spreadsheet, and the “Last” fields from the spreadsheet.
You can change the formula, as shown below, to make the “Last” field a relative cell reference instead, and then copy that formula into a number of cells.  When you change the contents of the referenced cell, it will pull in the “Last” price  for the referenced cell as shown below:

The formula now shows: Create an RTD link from Quotes, and bring in the “Last” price from whatever symbol you enter in cell A1

Excel7

Category: Excel, OptionsTags: