5 minutes reading time (907 words)

Market Data in Excel from TrueFX

cover

TrueFX is a provider of real time forex rates between the most liquid currencies. Both current prices and bid/offer quotes are streamed as quoted by various brokers. 


Deriscope Initialization 

Deriscope is an Excel AddIn specializing in derivatives valuation and live feeds acquisition in Excel. Its newest version also allows you to receive data from TFX (TrueFX).

Deriscope comes with an integrated wizard in the form of a dedicated Excel taskpane as shown below that helps you generate various spreadsheet formulas, for example those required to fetch live feeds from TFX. 

TFX access through Deriscope requires that a user name and password have been set in its configuration section, which you can acquire from the TrueFX website and then enter them in the wizard's Settings entry labeled User Name and User Password, as the video below demonstrates:


Getting Live Quotes through Manual, Synchronous Request 

You can easily let the wizard generate and paste the formulas that fetches on demand live data from the TFX server and outputs them in the spreadsheet by clicking on the Tools button and choosing the appropriate menu items, as shown in this picture, where the wizard has been temporarily transferred to the left so that the cascading menu items do not overlap with each other:

After you click on the Synchronous (extended) menu item, the appropriate formulas will be pasted at and below the currently selected cell. For example, if cell A1 is currently selected, the result will be the array formula =dsLiveGetSync("TFX";A2:A11;B1:I1;FALSE) inserted in range B2:I11 as shown below: 

This formula is volatile, meaning it is primarily intended for spreadsheets set in manual calculation mode and is refreshed when you explicitly recalculate the containing worksheet. It takes as input a column containing the tickers and a row containing the requested output fields. It returns the corresponding TFX feeds. You may add as many rows as you wish and also more columns in order to display additional fields.

Currently TFX supports 11 different fields. You can easily change a given field by selecting the containing cell and choosing an item out of the displayed dropdown list, as shown below: 


Getting Live Quotes through Automatically Recurring Asynchronous Requests

Deriscope has formulas that display in spreadsheet live data that keep being automatically updated according to a specified time interval in a manner that does not interfere with other spreadsheet activities. This live data acquisition is called asynchronous and the required formulas can be pasted through the menu option Asynchronous (extended). This pastes the appropriate formulas at and below the currently selected cell. For example, if cell A1 is currently selected, the result will be a regular formula inserted in cell A1 and an array formula inserted in range B2:I11 as shown below: 

Cell A1 contains the formula =dsLiveStartEngine("TFX";5;A2:A11;B1:I1) that starts the live engine, effectively a repetitive request of live data from the TFX server. The number 5 indicates the number of seconds between successive requests. Deriscope sets it by default to 5 seconds but you may set it to as low as 1 second.

The function returns the text &LiveTFX, which is the handle name of an object that contains information about the current status of the live engine. If you select cell A1 this object's contents become visible inside the wizard, as the previous image demonstrates.

Far more important is the array function {=dsLiveGetAsync("TFX")} pasted over the range B2:I11, shown in the image below: 

This array function is declared volatile and upon calculation returns the latest feeds acquired by the live engine. Although the live engine keeps receiving feeds from the server, the array function normally does not run by itself, so the feeds are generally not displayed as they are received. At the time when this function was last calculated, no feeds had been received and therefore only zeros are being displayed.

You may press SHIFT-F9 to calculate the sheet in order to get the currently available feeds or more conveniently press the yellow highlighted Auto Refresh button shown in the above image to set an automatic recalculation of that array function as soon as new feeds arrive.

The video below is a continuous loop of a 10-second recording of the spreadsheet when the update interval is set to 1 second. The seen colors are an optional Deriscope visual indication of the direction of any changes occurred in the displayed data: Green for upticks, red for downticks and yellow for non-numeric changes. 


Forex Trading Simulator in Excel

You can download from the Deriscope download site a special spreadsheet that allows you to enter BUY and SELL simulated orders on the actual real time bid/ask quotes from TFX. Your P&L that results from these simulated transactions is shown so that you can test if you are able to achieve a consistent profit under real and current market conditions, before you start trading with real money.

More details in this article.

The following two images show the operational and P&L sections of the spreadsheet: 


You may download the spreadsheet produced with the above steps here. 

File Name: LiveFeedsTrueFX
File Size: 15 kb
Download File

Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com 

Market Data in Excel from IEX
Market Data in Excel from Alpha Vantage