The History selection in the ProphetX Excel Add-in retrieves historic data for the symbol or expression entered in the Symbol List. With History you can also specify the order the data will be displayed, the time interval and how far back you want to retrieve the history. In addition, the history can also be converted to currency or units values.
- Select the History Button
The history dialog box will open.
- Add Symbol or Expression: type the symbol or expression and either click on the + button or press Enter to add it to the Symbol List. The drop-down arrow in the entry area also lists any previous symbols or expressions that were used for history. Or, look up the symbol by clicking on the Search button. (The Symbol Search works just like the Symbol Search in ProphetX – the only difference is that after you highlight the symbols you want to use, click on the Add button to add them to the symbol list).
– To enter a futures chain, enter the root symbol followed by a space and the number of contracts you want to include. Then press either the F3 key, Enter key, or the + icon button. The generated symbols will use the @1 @2 @3 notation. - Symbol List: to edit the symbol list, select a symbol and use the up/down icon buttons to change the order of the symbols, or the “X” icon button to delete. You can right-click the symbol and select an action to perform on the selected symbol (see Action Button below for more information).
- Action button: allows you to add additional information to the data you are retrieving and is described in detail under Symbol and Action Button.
- Fields: After symbols have been added to the Symbol List, you can select the fields you want to display. Click on the + icon button to add fields. The up and down arrow icon buttons will move fields into your preferred order. Right click on a field and select Delete to remove it from the list or click the “X” icon button.
- Show Average: select to show the Average of the displayed prices.
- Show Standard Deviation: select to show the Standard Deviation of the displayed prices.
- Layout: specifies whether you want the field orientation to display vertically or horizontally.
- Time: determines the historical interval you want displayed.
- Interval: prices retrieved or either daily, weekly, monthly, or intraday periods of 1, 5, 10, 15, 30, or 60 minutes.
- Range: allows the selection of the amount of data to retrieve.
- Most Recent: retrieves X number of prices from the current day. Enter the number of prices to retrieve i the “Periods Back” field.
- Fixed Range: retrieves a fixed number of prices from a Start and End date. Note that the End date can be in the future and the prices will fill in as the current date approaches the End date. Enter the date range in the “Start” and “End” fields.
- Expanding Range: the prices retrieved “expand” as new prices update. Note that this will overwrite the rows beneath the range as the new prices are updated. Enter the date/time to begin the range in the “Start” field.
- Expand on Close: the prices retrieved “expand” as the closing price is updated for the current day. Ideally used for daily futures prices when waiting for the Settle price. Enter the date/time to begin the range in the “Start” field.
- Order: selects the order to sort the data as “Newest First” or “Oldest First.”
- Blanks: when blank prices appear in the range, this setting allows selection of how it should be displayed. Blank prices can appear for many reasons: when showing weekends and holidays and the chosen instrument does not have prices for these days, the instrument may not trade during the select time period, etc.
- Show as “—“: this is the original ProphetX method of showing blank data. We kept this method because some users may have written routines that respond to this method.
- Leave blank: nothing is displayed.
- Use #N/A: this is the Excel method of showing blanks. When creating an Excel chart, this method will ignore the blanks in the chart. The other two methods will show a 0 (zero) value for the price.
- Fill: when blank prices are encountered, they can be filled in with existing prices,
- None: leave blank.
- Fill Forward: fill the blank prices with the previous price, but will not fill in future prices. For example, you select a Fixed Range of prices that contain future prices … those that range past the current date/time. Those future prices will remain blank until that date/time has passed.
- Fill Forward Extended: fill the blank prices with the previous price, and fill in future prices. For example, you select a Fixed Range of prices that contain future prices … those that range past the current date/time. Those future prices will be filled in with the most current price available.
- Fill Backward: fill the blank prices with the following price.
- Days: allows the trading days to be changed.
- Trading Days: display the exchange trading days/hours for the instrument.
- Weekdays with Holidays: display all weekdays (Monday-Friday) including holidays.
- Calendar Days (7-day): display all days including weekends and holidays.
Changing the display
Once you have the data in your spreadsheet, you can quickly edit it.
- Double-click on the top left cell of your query.
- The dialog box for the display will pop open and you can change any attributes you currently have, as well as add new ones.
Note: To adjust RTD links, refer to RTD Linking History.