DATASTREAM ADVANCE FOR OFFICE 5 USING EXCEL 2007 ISSUE 4
Datastream Advance for Office CONTENTS 1 Using Ribbon based UI for AFO (in MS-Office 2007 and above) 3 2 Using Excel with DATASTREAM Advance for Office 5 Creating data requests in Excel 5 Creating static data requests 7 Selecting datatypes 8 Selecting lists 9 Selecting expressions 9 Creating time series requests 10 Using lists with time series requests 10 Using functions and expressions with time series requests 11 Selecting expressions 11 Selecting dates 12 Creating Expression usin
Using Excel with Datastream Advance for Office 1 USING RIBBON BASED UI FOR AFO (IN MS-OFFICE 2007 AND ABOVE) When you install Datastream.xlam component, Datastream is available as an Add-In tab. Datastream provides following facilities in the form of seven separate groups. Figure: Datastream.xlam Note AdvanceOffice.xlam is also available in the installation folder.
Using Excel with Datastream Advance for Office Utilities: This group consists of six buttons. Find the series and datatype you require, using the extensive facilities available in Navigator and return these values to your spreadsheet. Create, calculate and select User Created Indices on your Datastream ID. View and create the saved expressions on your Datastream ID. Create a local, central system (l#), or User Created Index (x#) list from Datastream, SEDOL or ISIN codes in a range of cells.
Using Excel with Datastream Advance for Office 2 USING EXCEL WITH DATASTREAM ADVANCE FOR OFFICE CREATING DATA REQUESTS IN EXCEL Datastream Advance for Office (AFO) enables you to create and issue data requests from within Excel, rather than creating your data requests in Datastream and then embedding them in Excel. When you issue a request from Excel, it is passed directly to Datastream, bypassing Datastream. The results are returned as dynamic, refreshable objects.
Using Excel with Datastream Advance for Office Page 6 of 38
Using Excel with Datastream Advance for Office CREATING STATIC DATA REQUESTS Static data requests are created using the Datastream Static Request dialog to enter your request parameters. Choose Static Request from the Datastream menu The minimum you need to do to create a static data request is select a series, a datatype, and a start date on the Datastream Static Request dialog. You can then Submit the request to Datastream.
Using Excel with Datastream Advance for Office Selecting datatypes The Datatype Mnemonic Picker, displayed by clicking the Datatypes button, enables you to select individual datatypes per request. When you select a datatype, the Datastream mnemonic for the Datatype, for example, MV for Market Value, is copied to the Datatypes field on the Request dialog. Multiple selections are automatically separated by commas.
Using Excel with Datastream Advance for Office Selecting lists You can use lists stored on your PC (i.e. Local Lists) or remote lists stored on the Datastream host, rather than selecting a number of individual series. These lists include: Local Lists created in Datastream or Advance for Office, Datastream lists that have been downloaded onto your PC for use in Datastream. User lists (i.e. L#xxxx lists) that are stored on the Datastream host system. Constituent lists; for example, LFTSE100.
Using Excel with Datastream Advance for Office CREATING TIME SERIES REQUESTS You can create time series requests for individual series (e.g. price indices, exchange rates, and expression, etc.) as well as for your Datastream Local Lists, User created lists, and Datastream constituent lists. Unlike static data requests, you can specify a time period which can start as far back as data is held for a specific stock right up to the latest value.
Using Excel with Datastream Advance for Office USING FUNCTIONS AND EXPRESSIONS WITH TIME SERIES REQUESTS Selecting expressions For time series data requests, you can browse and select expressions stored on the Datastream host system. The Time Series Data Request Expression Picker, displayed by clicking the Expression button , on the Time Series Request screen, can display all user created expressions or Datastream global expressions, or both. See Using Expressions.
Using Excel with Datastream Advance for Office Selecting dates Dates can be typed as either a fixed date format, that is dd/mm/yy, or as a number of days (D), weeks (W), months (M), quarters (Q), or years (Y) ago from today, for example -3D which is three days ago from today. Note If the Date field is left blank, the last value (which may be prior to the date of the request) will be retrieved. You can also enter TODAY in the Date field to retrieve the latest intraday values.
Using Excel with Datastream Advance for Office 4. Click OK, the expression is displayed in Datatypes / Expression text field OR If you know the function name already, then you can directly enter the function name in Datatypes/Expressions text field, and can use Note button for completing the expression. You can use Help on this function link for more details. Using dropdown feature 1. Choose Time Series Request or Static Request from the Datastream menu. 2.
Using Excel with Datastream Advance for Office USING EXPRESSION EDITOR IN STATIC AND TIME SERIES DIALOGS The Expression Editor helps you to edit long strings you have entered in the Series/Lists (Time Series dialog) or the Datatypes/Expressions (Static dialog) text boxes in Excel. The Expression Editor is available whether Intellisense is “on‟” or “off”.
Using Excel with Datastream Advance for Office When you click the Expression Picker button in the Edit Series/Lists (Time Series dialog) the Expression Picker is displayed showing All the expressions on the ID. When you return the expression to the Editor it returns the name of the Expression with symbolic placeholders “X”, “Y”, “Z”, “A” replaced by Series1, Series2, Series3 or Series4, as appropriate.
Using Excel with Datastream Advance for Office CREATING COMPANY ACCOUNTS DATA REQUESTS In addition to static and time series data, Datastream and Worldscope company accounts data can also be requested and displayed within Excel. The request creation facilities specific to company accounts data enable you to retrieve company accounts data for equities, Local Lists, and constituents over a specified time period.
Using Excel with Datastream Advance for Office Select last request parameters You can re-insert the parameters for any of your previously used requests by right clicking the mouse over the Series Navigation button and selecting the request details from the displayed list, for example: Selecting the request details from this list inserts all the details in the relevant fields. Click Submit to issue the request.
Using Excel with Datastream Advance for Office Select, or deselect, the required format options and click the Submit button. The selected options are displayed when you make a request. Click the Default Option button to return the format options back to their default settings, which are made on the Options dialog from the Datastream menu. Select Display Data as MS Chart to chart your data using Microsoft Chart - if the embed option is selected, a Refresh button is placed in the chart.
Using Excel with Datastream Advance for Office Use the following options to edit the request: Cut Cuts the entire details of the embedded request to the Windows clipboard for pasting into other worksheets or workbooks. Note that when using the Cut, and subsequent Paste options, only a copy of the data is pasted into the new location or worksheet, not the underlying embedded request. Copy Copies the selected data request for pasting into other worksheets and workbooks.
Using Excel with Datastream Advance for Office To select these options: 1. Choose Options from the Datastream menu. The Options dialog is displayed. 2. Select the options you require by checking the boxes: Request format options These options define the default display options for data requests in Excel. They can be overridden by the request format options on the Static Data Request, Time Series Request and Company Accounts Request dialogs.
Using Excel with Datastream Advance for Office T1 code - displays Thomson ONE code for the requested series. It is available only when Datastream is embedded in the Thomson ONE workstation and currently applies to the Equities only. Transpose - displays individual time series data across the Excel spreadsheet in rows. This is particularly useful if you are requesting data over very short time periods or lower reporting frequencies, that is quarterly or yearly.
Using Excel with Datastream Advance for Office Expression editing Select the Show out of date warning to warn you when expressions have not been refreshed. Intellisense – This option enables you to see the functions and parameters available for the functions with links to the help files. Function picker – This option enables you to search and pick the functions and parameters available.for the functions with links to the help files.
Using Excel with Datastream Advance for Office 3 USING LISTS CREATING A LOCAL LIST FROM A SET OF CODES (DATASTREAM, ISINS, SEDOLS, ETC) IN EXCEL (FOR EXAMPLE FROM A BACK OFFICE SYSTEM) To create a local list: 1. Select Create List from the Datastream>Tools menu. 2. The Datastream List Creation dialog is displayed. 3. Select the Code Range from the Code Range field. 4. Enter the list description in the List Description field. 5. Enter the file name in the List File Name field. 6.
Using Excel with Datastream Advance for Office 4 USING EXPRESSIONS THE EXPRESSION PICKER You use the Expression Picker to select expressions. Click the Expression Picker button, to display the Expression Picker dialog. The Expression Picker dialog Show: Use the Show: drop down box to display: all, local, or global expressions. Search Type either the first few letters of the title, or expression in the Find: field.
Using Excel with Datastream Advance for Office Tools available in the Expression Builder Function The Select a Function dialog is displayed. Select a specific Datastream function, the function and its parameters (e.g., start and end dates) are inserted into the workspace area. Existing expression The Expression Picker dialog is displayed. Select an existing Datastream global expression or local expression to add to your expression. For example, if you want to nest one expression into another.
Using Excel with Datastream Advance for Office 5 USING THE REQUEST MANAGER You can create and embed any number and type of data and chart requests in a single worksheet. For example, a worksheet could contain an embedded time series data request, a static data request, a company accounts data request, and an embedded Datastream chart all for the same series and time period.
Using Excel with Datastream Advance for Office 3. To change the time period, click on Time Period button. To change display properties, Right click on the chart in Datastream and select Properties option. The Default Chart Properties dialog will appear. Customise the display for your chart by selecting the options available.
Using Excel with Datastream Advance for Office 4. Right click on the chart and click Update Chart to transfer the updated chart back to Excel in the same place. CHANGING THE PROPERTIES OF A CHART USING VBA Experienced users can use Excel‟s VBA Design Mode to access and edit the properties of embedded charts the Properties dialog. On the ChartControl Properties dialog you can change the request description, request string, autorefresh selection and chart dimensions.
Using Excel with Datastream Advance for Office 6 USING THE REQUEST TABLE WHAT IS THE REQUEST TABLE? If your use of Excel involves building models using several requests for data from Datastream, you can use the Request Table to administrate and edit these refreshable data requests. The request table is an Excel template with additional facilities for entering request parameters as individual Excel rows.
Using Excel with Datastream Advance for Office CREATING A SET OF REQUESTS To create a set of requests in a Request Table: Note: You can insert the resulting data, using Data Destination, into several worksheets within the same or different workbooks. 1. Select the New Request Table option from the Datastream >Request Table menu. 2. You are now prompted to save the request table under a new name. Click Yes, supply the Request Table name and directory location, and click Save.
Using Excel with Datastream Advance for Office Request Table options. This feature is primarily intended to provide compatibility with earlier versions of the Request Table. If the Always Include Currency option is selected, any format options selected will have the currency symbol ($) appended to the worksheet code; for example RC$. 5. Select series or lists (i.e. Local Lists, Datastream mainframe lists and constituent lists) using the Series Lookup buttons.
Using Excel with Datastream Advance for Office new row or column). If you select this option, any future processing of the Request Table will take into account the new cell location. If you do not select this option, the data will always be inserted at the same cell location. If the destination reference is to a closed external workbook, you must ensure that the Display Destination as Excel Formula check box is selected to enable the request table to open the workbook.
Using Excel with Datastream Advance for Office UPLOAD LIST BUTTON The Upload List button in the request table can be copied to a sheet that has back office codes to upload. There are two modes that can be configured by right clicking on the button and selecting the list option. You can create either local or mainframe lists using the Create a new list option, Click here for more details.
Using Excel with Datastream Advance for Office PROCESSING AND SCHEDULING REQUESTS You can choose to either process your Request Table straight away or schedule the processing at some date and time in the future. To process your Request Table straight away, click the Process Table button. This will launch the DSAPI which will pass the request details to Datastream in the order that they appear in the list.
Using Excel with Datastream Advance for Office USER CREATED TIME SERIES This workbook also includes a Request table to enable series to be downloaded manipulated - and then uploaded these series back to Datastream to be charted. A user time series is a series of values (data) for different points in time created by you and uploaded for storage on Datastream. The values can be daily, weekly, monthly, quarterly, or yearly and you can save the series in management groups to help you organise them.
Using Excel with Datastream Advance for Office 1st - Plot data from the beginning of the calendar period. Mid - Plot data from the middle of the calendar period. End - Plot data from the end of the calendar period. Carry ind. For null values. You can leave the values blank or carry forward the previous value. Yes - carries the previous value. No - leaves the value at null. Upload type When editing a series, you can upload only the values that have changed. Full - uploads all the values in the series.
Using Excel with Datastream Advance for Office 12. Set the date range. Click the Date range button and type the start and end dates. Select the frequency of the data: Daily, weekly, monthly, quarterly, or yearly. The dates are displayed in the date range column, ready for values to be added. 13. Type your values against the dates displayed. 14. Click the Upload button to save your series. Note the cursor must be in the Code field to upload the series.
© 2009 Thomson Reuters. All rights reserved. Republication or redistribution of Thomson Reuters content, including by framing or similar means, is prohibited without the prior written consent of Thomson Reuters. 'Thomson Reuters' and the Thomson Reuters logo are registered trademarks and trademarks of Thomson Reuters and its affiliated companies. For more information Send us a sales enquiry at reuters.com/sales enquiry Read more about our products at reuters.
