Active Trader Magazine

Technology for Traders

Using Microsoft Excel data visualization techniques

By Thom Hartle

Many Independent Software Vendors (ISVs) provide the ability to import real-time market data to Excel by either Real-Time Data (RTD) or Dynamic Data Exchange (DDE). These two services enable traders and analysts to create custom Excel dashboards and perform market analysis that might not otherwise be possible using the vendor’s software platform alone.

However, while most Excel devotees are often very knowledgeable about the process of using RTD or DDE formulas to display market information in a spreadsheet, they often miss the opportunity to create more useful and sophisticated tools. Using Excel’s suite of Conditional Formatting applications allows you to create a more informative and visually intuitive market data display.

For example, Excel has a “heat mapping” feature that allows you to assign specific colors to indicate percentage changes — for example, you can format a percent change column to highlight the top market performers bright green and highlight the worst market performers bright red. This makes the information much easier to take in at a glance and frees you from having to do math in your head. Your eyes go immediately to the extreme over-and under-performers.

For the complete article, see the October 2012 issue of Active Trader magazine. Click here to subscribe.

email this story
print this story