.. index::
single: G7 Reference Manual; X
single: G7 Command; xl
single: G7 Command; xl open
single: G7 Command; xl create
single: G7 Command; xl save
single: G7 Command; xl write
single: G7 Command; xl vecwrite
single: G7 Command; xl formula
single: G7 Command; xl font
single: G7 Command; xl setfont
single: G7 Command; xl read
single: G7 Command; xl vecread
single: G7 Command; xl mkseries
single: G7 Command; xl matread
single: G7 Command; xl missing
single: G7 Command; xl print missing
single: G7 Command; xl clear missing
single: G7 Command; xl replace
single: G7 Command; xl visible
single: G7 Command; xl invisible
single: G7 Command; xl name
single: G7 Command; xl column
single: G7 Command; xl row
single: G7 Command; xl close
single: G7 Command; xl exit
single: G7 Command; xl graph
single: G7 Command; xl merge
single: G7 Command; xl subscript
single: G7 Command; xl border
single: G7 Command; xl gridlines
single: G7 Command; xl printer
single: G7 Command; xl cf
single: G7 Command; xl setfrequency
single: G7 Command; xl freeze
single: G7 Command; xl background
single: G7 Command; conditional formatting
*G7* Commands: X
================
.. _G7RMxl:
| **xl**
| The *xl* command actually is a family of two or three word commands that enable the reading and writing of Excel worksheets from within *G7*. Actually, *G7* does not do the reading and writing itself, but communicates with the Excel program and provides instructions for Excel to execute. Therefore, Excel already must be installed on your machine before using these *xl* commands.
Next, some examples will be provided that show how to read from and write to Excel files. For details on each member of this family of commands, see the corresponding entries in this Reference Manual.
Example 1: In this example, the file "XLTEST.XLS" is created in the current directory. The time series are written to the spreadsheet by giving the starting cell, the direction to write the data (down or right), the name of the series, and the range of periods to write. ::
fdate 1975 2010 # Create sample data.
f Year = 1974 + @cum(t, 1.0, 0.0)
f Data = 1975 / (t - 1975)
xl create xltest.xls # Start Excel server, create xltest.xls workbook.
xl open worksheet 1 # Open worksheet 1.
xl write A 1 "Writing text to file:"
xl write A 3 "Year" # Record label for the year
xl write A 4 down Year 1976 2010 # Record the year
xl write B 3 "Data" # Record series name
xl write B 4 down Data 1976 2010 # Write series 'Data'
xl close # Close the workbook.
Example 2: In this example, we re-open the same file, and read one of the series back into *G7* with a different name. ::
xl open xltest.xls # Open the workbook.
xl open worksheet 1 # Open worksheet 3.
xl read A 1 "" # Read the string in position A1; string
# will be printed on screen.
xl read B 4 right Year2 1976 2010 # Read data into workspace.
xl exit # Close workbook, close connection to
# Excel server.
Example 3: In this example, we must be working with a Vam file, and it must be opened and declared to be the default. The data read from the spreadsheet are stored as vector elements in the Vam file. Writing to the Vam file, instead of to the *G7* workspace bank, is forced by providing a bank leter ('c') corresponding to the open Vam bank in front of the name of the vector. Note that columns may be specified either by the Excel column letters or by the column number. ::
xl open C0301e.xls
xl open worksheet 1
do{
xl read %1 27 down c.gdpN%2 1990 1990
}(3-4 6-7 9-10)(1-6)m
xl exit
Example 4: In this final example, we show the use of the "xl matread" command. The command must all be on one line, even though it seems to span two lines in this example. First the blocks of data in the Excel spreadsheet are specified by listing their rows and columns. Next, the name of the matrix is given in which the data should be sotred, along with the matrix rows and columns. Finally, the year is given for which the matrix should be stored. ::
xl open C0319e.xls
xl open worksheet 1
xl matread c(2-18) r(14-17, 19-20, 22-24,26-29,31-32, 34-35)...
c.AM c(1-17) r(1-17) 2000
Related Topics: 123toG, :ref:`p123 <G7RMp123>`
.. _G7RMxlopen:
| **xl open <filename>**
| This command opens an Excel file, either for writing or reading.
| **xl open worksheet <worksheet>**
| **xl open chart <chart>**
| This command opens a worksheet or chart within the Excel file for reading or writing. The number to be used is the order of the worksheets within the file, where the first tab is '1'. The list of charts is handled separately, and the first chart also begins with '1'. Alternatively, the name of the worksheet or chart sheet may be specified. "worksheet" may be abbreviated "ws" and "chart" may be abbreviated "ch".
.. _G7RMxlcreate:
| **xl create**
| **xl create workbook [<filename> [<filetype>]]**
| **xl create [before|after] worksheet [<worksheet>]**
| **xl create [before|after] chart [<chart>]**
| An *xl create* command with no arguments launches the Excel server and opens a new workbook with one worksheet.
An *xl create workbook* command may provide the name for a new Excel filename. If no name is provided, then a new workbook will be created with the default name. The option "workbook" may be abbreviated "wb".
The default filetype is XLS. Available file types include
===================== =====================
**File Types**
--------------------- ---------------------
AddIn (.xlam) WorkbookNormal (.xls)
CSV (.csv) SYLK (.slk)
CSVMac (.csv) Template (.xltx)
CSVMSDOS (.csv) TextMac (.txt)
CSVWindows (.csv) TextMSDOS (.txt)
DBF2 (.dbf) TextPrinter (.txt)
DBF3 (.dbf) TextWindows (.txt)
DBF4 (.dbf) WK1 (.wk1)
DIF (.dif) WK1ALL (.wk1)
Excel2 (.xls) WK1FMT (.wk1)
Excel2FarEast (.xls) WK3 (.wk3 )
Excel3 (.xls) WK4 (.wk4)
Excel4 (.xls) WK3FM3 (.wk3)
Excel5 (.xls) WKS (.wks)
Excel7 (.xls) WQ1 (.wq1)
Excel9795 (.xls) UnicodeText (.txt)
Excel4Workbook (.xls) Html (.html)
IntlAddIn (.xla) XLS (.xls)
IntlMacro (.xlsm)
===================== =====================
If no extension is provided with the filename, then the appropriate extension will be determined according to the file type and will be appended to the file name.
An *xl create worksheet* command may provide a name for a new worksheet to be added to the open workbook. The instruction "worksheet" may be replaced with "ws". An *xl create chart* command may provide a name for a new chart sheet to be added to the open workbook. The instruction "chart" may be replaced with "ch". If "before" or "after" is specified, then the new sheet will be inserted to the left or right of the currently-active sheet. See also the :ref:`xl name <G7RMxlNameWorksheet>` command.
.. _G7RMxlbackgroundcolor:
| **xl background <color>**
| Set the background color. Available colors are listed in the `xl font <G7RMxlfont>` section.
.. _G7RMxlcellborder:
| **xl border <FC> <FR> <LC> <LR> <option 1> [<option2>[...]]**
| Set the cell border, where the cell range is given by <first column>, <first row>, <last column>, and <last row>, and options are
:off: remove border.
:color: border color, chosen from the list of Excel colors.
:weight: border weight, chosen from hairline, thin, medium, or thick.
:position: border position, chosen from border[default], left, right, top, bottom, horizontal, vertical, diagonalup, or diagonaldown.
:linestyle: border linestyle, chosen from continuous, dash, dashdot, dashdotdot, dot, double, or slantdashdot.
.. _G7RMxlfreeze:
| **xl freeze <[row]|[column]|[cell]|[off]> [[r | c | rc] [rc]]>**
| Freeze a spreadsheet at a specified location, where first the <[row]|[column]|[cell]> for freezing is given, then the <[upper-row]|[leftmost-column]|[top-left-cell]> is given for the frozen pane, and finally the the top-left cell for the lower-right scrolling pane is specified. Alternatively, freezing may be turned off with the "off" option.
.. _G7RMxlchart:
| **xl graph <row 1><column1>...<row N><column N> <direction> <start date><end date> <graph style>**
| **xl graph title ["<title>" [font <style>]]**
| Create a graph sheet in the current workbook. The graph will appear in a sheet to the left of the active worksheet. Specify the first cell of each series to be graphed. Each series must extend in the same direction, either to the right or down the sheet. Each series should have the same number of observations. The first series will appear on the horizontal axis. Current *G7* settings for title, subtitle, and vertical axis title will be employed.
The command also may be used to recover the title of an existing graph; if no arguments are given, then the graph title subsequently is available using the %xls keyword. If a title is given, then the title will be added to the active chart; the title must be surrounded by quotation marks. If the title is followed by the "font" keyword, then font options may be specified including color, typeface, size, single or double underline, bold, and italic.
Available graph styles include:
==================================== ================================
**Graph Styles**
----------------------------------------------------------------------
ColumnClustered Bubble3DEffect
ColumnStacked StockHLC
ColumnStacked100 StockOHLC
3DColumnClustered StockVHLC
3DColumnStacked StockVOHLC
3DColumnStacked100 CylinderColClustered
BarClustered CylinderColStacked
BarStacked CylinderColStacked100
BarStacked100 CylinderBarClustered
3DBarClustered CylinderBarStacked
3DBarStacked CylinderBarStacked100
3DBarStacked100 CylinderCol
LineStacked ConeColClustered
LineStacked100 ConeColStacked
LineMarkers ConeColStacked100
LineMarkersStacked ConeBarClustered
LineMarkersStacked100 ConeBarStacked
PieOfPie ConeBarStacked100
PieExploded ConeCol
3DPieExploded PyramidColClustered
BarOfPie PyramidColStacked
XYScatterSmooth PyramidColStacked100
XYScatterSmoothNoMarkers PyramidBarClustered
XYScatterLines PyramidBarStacked
XYScatterLinesNoMarkers PyramidBarStacked100
AreaStacked PyramidCol
AreaStacked100 3DColumn
3DAreaStacked Line
3DAreaStacked100 3DLine
DoughnutExploded 3DPie
RadarMarkers Pie
RadarFilled XYScatter
Surface 3DArea
SurfaceWireframe Area
SurfaceTopView Doughnut
SurfaceTopViewWireframe Radar
Bubble
==================================== ================================
.. _G7RMxlconditionalformatting:
| **xl cf <FC> <FR> <LC> <LR> <number of conditions>**
| **<type1><operator><condition1>[<operator><condition2>][font <opts>][border <opts>][background <opts>]**
| **[<type2><operator><condition1>[<operator><condition2>][font <opts>][border <opts>][background <opts>]]**
| **[<type3><operator><condition1>[<operator><condition2>][font <opts>][border <opts>][background <opts>]]**
| Set conditional formatting for the specified range of cells. Up to three conditions may be specified, and so the number of conditions and the number of specification lines must be between 1 and 3.
Each row of conditions must begin with "value"; "formula" will be offered later. Operators must be <=, <, ==, !=, >, or >=. If a second condition is specified and the first operator is < or <=, then the second operator must be > or >= (not between), or if the first operator is > or >=, then the second operator must be < or <= (between); otherwise, any second operator and condition will be ignored. A condition may be a number, string, or cell address. Available format settings include font and background; the "font" or "background" keyword must preceed the format specification, where specifications may be chosen from the relevant list. Font options include color, bold, italic, and single or double underline; border is not implemented yet; background options include color. The number of conditions must match the number of rows.
.. _G7RMxlgridlines:
| **xl gridlines [<on|off>] [<color>]**
| Turn on or off the display of gridlines on the selected worksheet. A color may be specified from the list of Excel colors.
.. _G7RMxlmerge:
| **xl merge <FC> <FR> <LC> <LR> ["off"]**
| Merge cells in the rectangle from column <FC> and row <FR> to column <LR> and row <LR>. Separate cells by adding "off".
.. _G7RMxlprinter:
| **xl printer <option 1> [<option2>[...]]**
| Set print options, where the options are
:orientation: set the page orientation to landscape or portrait.
:area: set the print area with "area <fr><fc><lr><lc>", or turn off the print area with "off".
:print: print the current page.
| **xl save [<namefile> [filetype]]**
| An *xl save* command must provide a name for the open workbook, if the workbook has not been named already.
If a file type is specified that is different than the current setting, then the spreadsheet will be saved as the new file type. The default filetype is .XLS. Available file types include
===================== =====================
**File Types**
--------------------- ---------------------
AddIn (.xlam) WorkbookNormal (.xls)
CSV (.csv) SYLK (.slk)
CSVMac (.csv) Template (.xltx)
CSVMSDOS (.csv) TextMac (.txt)
CSVWindows (.csv) TextMSDOS (.txt)
DBF2 (.dbf) TextPrinter (.txt)
DBF3 (.dbf) TextWindows (.txt)
DBF4 (.dbf) WK1 (.wk1)
DIF (.dif) WK1ALL (.wk1)
Excel2 (.xls) WK1FMT (.wk1)
Excel2FarEast (.xls) WK3 (.wk3 )
Excel3 (.xls) WK4 (.wk4)
Excel4 (.xls) WK3FM3 (.wk3)
Excel5 (.xls) WKS (.wks)
Excel7 (.xls) WQ1 (.wq1)
Excel9795 (.xls) UnicodeText (.txt)
Excel4Workbook (.xls) Html (.html)
IntlAddIn (.xla) XLS (.xls)
IntlMacro (.xlsm)
===================== =====================
If no extension is provided with the filename, then the appropriate extension will be determined according to the file type and will be appended to the file name.
.. _G7RMxlsubscript:
| **xl subscript <C> <R> "<text>"**
| **xl superscript <C> <R> "<text>"**
| Both append "<text>" to the current contents of the cell at column c and row <R>, either as subscript or superscript.
.. _G7RMxlwrite:
| **xl write <column letter> <row number> "<text>"**
| **xl write <column_letter> <row_number> <down|right|up|left> <series_name> [<start_date> <end_date>]**
| The first specification writes a string of text to a cell in the worksheet.
The first specification will print floating point values or integers to the spreadsheet when numbers are provided as the text argument. Previously, numbers were written as strings, though printing of strings still can be forced with "'<number>".
The second specification writes a time series into the Excel worksheet, starting at the given location, going up or down a column, or across a row, for the dates given. If no dates are specified, then the current *tdates* settings will be employed.
.. _G7RMxlvecwrite:
**xl vecwrite <vector> < v(index) > < c(cols) > < r(rows) > < direction > [< start > [ end ]]**
*G7* can print entire vectors of data for multiple years using a single command. The *vecwrite* command must be used with a VAM bank. This command writes text or data to an open Excel worksheet, where:
:Vector: The root name of a vector stored in a vam bank. Bank letters are allowed.
:Index: The range of vector elements that are to be printed.
:Cols: The spreadsheet columns for the first period of data, given as a group of letters or numbers.
:Rows: The spreadsheet rows for the first period of data, given as a group of numbers. Either cols or rows must have a single element, and the other must have the same number of elements as contained in index.
:direction: Either d(own) or r(ight), starting with the row or column specified with the cols and row entries. NOTE: this is the direction indexed by time, so that if data for a particular series are to be written across a row, then "right" should be specified.
:Start: The starting date, where the date is in *G7* format. If dates are not provided, the desired dates are assumed to be those of the current *tdates* setting.
:End: The ending date, where the date is in *G7* format.
Examples::
xl vecwrite a.output v(1-10) c(B) r(5-14) right 2008 2010
.. _G7RMxlformula:
| **xl formula <column> <row> "< formula >"**
| Insert an Excel formula in the specified cell. ::
xl formula A 5 "=sum(A1:A4)"
.. _G7RMxlfont:
| **xl setfont <settings>**
| **xl font <column1> <row1> <column2> <row2> <settings>**
| **xl font <column1> <row1> <column2> <row2> <settings>**
| The *xl setfont* routine sets the font for all subsequent printing. The *xl font* command sets the font for the specified range of cells.
:clear: Clear the fonts set by the *setfont* command.
:bold: Set text to bold face.
:italic: Set text to italic.
:underline: See the list of underlining options.
:justify|center|right|left: Horizontal alignment.
:top|vcenter|bottom|vjustify: Vertical alignment.
:color: See the list of available text colors.
:type: face See the list of available type faces.
:X or sizeX: Set the font size to integer X.
Examples::
xl setfont bold italic ~ right top "red" "arial" size14
xl font A 1 D 10 "Courier New" 10 "blue"
xl font A 6 AZ 6 bold format("yyyy")
**textwrap [off]**
Turn text wrapping on or off for the specified cells.
**format(<format>|off)**
This setting allows specification of general, number, currency, accounting, date, short date, long date, time, percentage, fraction, scientific, text, off, or custom settings. Complex and multiple word settings must be wrapped in "". The "off" and custom settings must use the "format(<>)" syntax; other settings may be stated simply as "general", "number", or otherwise.
**separator("<separator>"|off]**
Turn on or off the use of a separator for numeric cells. A custom separator may be specified, or the "("<separator>")" argument may be omitted to employ the default separator. The setting will be stored, but it only will be applied to cells if a format specification has been or will be given. Precision still is controlled by the G7 format command.
**Underline**
Underlining may be specified in several ways.
| **underline [-]**
| **UnderlineSingle**
| **-**
| Single underlining.
| **underline =**
| **UnderlineDouble**
| **=**
| Double underlining.
| **underline _**
| **UnderlineSingleAccounting**
| **_**
| Single accounting underlining.
| **underline [ ~ ]**
| **UnderlineDoubleAccounting**
| **~**
| Double accounting underlining.
| **underline n**
| **UnderlineNone**
| No underlining.
.. _G7RMxlcolors:
Available font colors include:
=============== ==============
**Font Colors**
--------------- --------------
"None" "Medium Gray"
"Aqua" "Mint green"
"Black" "Navy blue"
"Blue" "Olive green"
"Cream" "Purple"
"Dark Gray" "Red"
"Fuchsia" "Silver"
"Gray" "Sky blue"
"Green" "Teal"
"Lime green" "White"
"Light Gray" "Yellow"
"Maroon" "RGB(<int1>,<int2>,<int3>)"
=============== ==============
System fonts may be specified by employing the "TF(<system_font)" function (or "typeface(<system_font>)") in <settings>. Multiple-word font names must be surrounded by quotation marks. For example, if the Adobe Garamond Pro font is installed, then it may be specified in the *xl font* command as "TF("Adobe Garamond Pro")". Available standard font types include:
================================= =====================================
**Font Types**
--------------------------------- -------------------------------------
"Agency FB" "Gill Sans Ultra Bold Condensed"
"Agency FB Bold" "Gloucester MT Extra Condensed"
"Algerian" "Goudy Old Style"
"Arial" "Goudy Old Style Bold"
"Arial Black" "Goudy Old Style Italic"
"Arial Black Italic" "Goudy Stout"
"Arial Bold" "Haettenschweiler"
"Arial Bold Italic" "Harlow Solid Italic"
"Arial Italic" "Harrington"
"Arial Narrow" "High Tower Text"
"Arial Narrow Bold" "High Tower Text Italic"
"Arial Narrow Bold Italic" "Impact"
"Arial Narrow Italic" "Imprint MT Shadow"
"Arial Rounded MT Bold" "Informal Roman"
"Arial Unicode MS" "Jokerman"
"Baskerville Old Face" "Juice ITC"
"Batang" "Kristen ITC"
"Bauhaus 93" "Kunstler Script"
"Bell MT" "Lucida Bright"
"Bell MT Bold" "Lucida Bright Demibold"
"Bell MT Italic" "Lucida Bright Demibold Italic"
"Berlin Sans FB" "Lucida Bright Italic"
"Berlin Sans FB Bold" "Lucida Calligraphy Italic"
"Berlin Sans FB Demi Bold" "Lucida Fax Demibold"
"Bernard MT Condensed" "Lucida Fax Demibold Italic"
"Blackadder ITC" "Lucida Fax Italic"
"Bodoni MT" "Lucida Fax Regular"
"Bodoni MT Black" "Lucida Handwriting Italic"
"Bodoni MT Black Italic" "Lucida Sans Demibold Italic"
"Bodoni MT Bold" "Lucida Sans Demibold Roman"
"Bodoni MT Bold Italic" "Lucida Sans Italic"
"Bodoni MT Condensed" "Lucida Sans Regular"
"Bodoni MT Condensed Bold" "Lucida Sans Typewriter Bold"
"Bodoni MT Condensed Bold Italic" "Lucida Sans Typewriter Bold Oblique"
"Bodoni MT Condensed Italic" "Lucida Sans Typewriter Oblique"
"Bodoni MT Italic" "Lucida Sans Typewriter Regular"
"Bodoni MT Poster Compressed" "Magneto Bold"
"Book Antiqua" "Maiandra GD"
"Book Antiqua Bold" "Map Symbols"
"Book Antiqua Bold Italic" "Matura MT Script Capitals"
"Book Antiqua Italic" "Mistral"
"Bookman Old Style" "Modern No. 20"
"Bookman Old Style Bold" "Monotype Corsiva"
"Bookman Old Style Bold Italic" "MS Mincho"
"Bookman Old Style Italic" "MS Outlook"
"Bradley Hand ITC" "MT Extra"
"Britannic Bold" "Niagara Engraved"
"Broadway" "Niagara Solid"
"Brush Script MT Italic" "OCR A Extended"
"Californian FB" "Old English Text MT"
"Californian FB Bold" "Onyx"
"Californian FB Italic" "Palace Script MT"
"Calisto MT" "Palatino Linotype"
"Calisto MT Bold" "Palatino Linotype Bold"
"Calisto MT Bold Italic" "Palatino Linotype Bold Italic"
"Calisto MT Italic" "Palatino Linotype Italic"
"Castellar" "Papyrus"
"Centaur" "Parchment"
"Century" "Perpetua"
"Century Gothic" "Perpetua Bold"
"Century Gothic Bold" "Perpetua Bold Italic"
"Century Gothic Bold Italic" "Perpetua Italic"
"Century Gothic Italic" "Perpetua Titling MT Bold"
"Century Schoolbook" "Perpetua Titling MT Light"
"Century Schoolbook Bold" "Playbill"
"Century Schoolbook Bold Italic" "PMingLiU"
"Century Schoolbook Italic" "Poor Richard"
"Chiller" "Pristina"
"Colonna MT" "Rage Italic"
"Comic Sans MS" "Ravie"
"Comic Sans MS Bold" "Rockwell"
"Cooper Black" "Rockwell Bold"
"Copperplate Gothic Bold" "Rockwell Bold Italic"
"Copperplate Gothic Light" "Rockwell Condensed"
"Courier New" "Rockwell Condensed Bold"
"Courier New Bold" "Rockwell Extra Bold"
"Courier New Bold Italic" "Rockwell Italic"
"Courier New Italic" "Script MT Bold"
"Curlz MT" "Showcard Gothic"
"Edwardian Script ITC" "SimSun"
"Elephant" "Snap ITC"
"Elephant Italic" "Stencil"
"Engravers MT" "Symbol"
"Eras Bold ITC" "Tahoma"
"Eras Demi ITC" "Tahoma Bold"
"Eras Light ITC" "Tempus Sans ITC"
"Eras Medium ITC" "Times"
"Felix Titling" "Times New Roman"
"Footlight MT Light" "Times New Roman Bold"
"Forte" "Times New Roman Bold Italic"
"Franklin Gothic Book" "Times New Roman Italic"
"Franklin Gothic Book Italic" "Trebuchet MS"
"Franklin Gothic Demi" "Trebuchet MS Bold"
"Franklin Gothic Demi Cond" "Trebuchet MS Bold Italic"
"Franklin Gothic Demi Italic" "Trebuchet MS Italic"
"Franklin Gothic Heavy" "Tw Cen MT"
"Franklin Gothic Heavy Italic" "Tw Cen MT Bold"
"Franklin Gothic Medium" "Tw Cen MT Bold Italic"
"Franklin Gothic Medium Cond" "Tw Cen MT Condensed"
"Franklin Gothic Medium Italic" "Tw Cen MT Condensed Bold"
"Freestyle Script" "Tw Cen MT Condensed Extra Bold"
"French Script MT" "Tw Cen MT Italic"
"Garamond" "Verdana"
"Garamond Bold" "Verdana Bold"
"Garamond Italic" "Verdana Bold Italic"
"Gigi" "Verdana Italic"
"Gill Sans MT" "Viner Hand ITC"
"Gill Sans MT Bold" "Vivaldi Italic"
"Gill Sans MT Bold Italic" "Vladimir Script"
"Gill Sans MT Condensed" "Wide Latin"
"Gill Sans MT Ext Condensed Bold" "Wingdings"
"Gill Sans MT Italic" "Wingdings 2"
"Gill Sans Ultra Bold" "Wingdings 3"
================================= =====================================
.. _G7RMxlread:
| **xl read <column letter> <row number> ""**
| **xl read <column letter> <row number> <direction > <series name> <starting date> <ending date>**
| **xl read <col1> <row1> date [<col2> <row2>]**
| The first specification will read the text in the cell at the specified column and row. The string of text will be printed to the screen. It also may be recovered by using the *%xls* keyword.
The second specification reads a time series from the Excel worksheet, starting at the given location and proceeding in the direction specified, for the dates given. <direction> may be right, down, left, or up.
The third specification introduces the ability to read a range of dates and then to recover the date and its components with keywords and to read a corresponding range of data with possibly non-contiguous dates. The set of dates are stored in Excel date format, where a single date may be recovered with the %xldate keyword. The same may be recovered in G7 date format with %xlgdate, or the date components may be recovered as %xlyear, %xlquarter, %xlmonth, and %xlday. If a range of cells is specified, then the range must contain either a single row or column. Before reading a subsequent data set using this date range, the frequency should be specified with the *xl setfrequency* command to prevent possible ambiguity, particularly with quarterly data. Finally, data may be read with a second *xl read* command that employs the stored date range.
Example::
xl read A 1 date H 1
xl setfrequency 4
xl read A 2 right gdp "xldates"
.. _G7RMxlvecread:
**xl vecread <c(columns)> <r(rows)> <direction> <vector> <v(elements)> [<start date> [<end date>]]**
*G7* can read entire vectors of data for multiple years using a single command. The *vecread* command must be used with a VAM bank. This command reads text or data from an open Excel worksheet, where:
:Vector: The root name of a vector stored in a vam bank. Bank letters are allowed.
:Elements: The range of vector elements that are to be printed.
:Cols: The spreadsheet columns for the first period of data, given as a group of letters or numbers.
:Rows: The spreadsheet rows for the first period of data, given as a group of numbers. Either cols or rows must have a single element, and the other must have the same number of elements as contained in index.
:direction: Either d(own) or r(ight), starting with the row or column specified with the cols and row entries. NOTE: this is the direction indexed by time, so that if data for a particular series are to be written across a row, then "right" should be specified.
:Start: The starting date, where the date is in *G7* format. If dates are not provided, the desired dates are assumed to be those of the current *tdates* setting.
:End: The ending date, where the date is in *G7* format.
Examples::
xl vecread c(B) r(7 9 11-18 20 21 23-29 31-43 45 47-50 52-58 60-64 66 68-77 79-153 155-157 159-236 238-268) right cr v(1-247) 2000 2008
.. _G7RMxlsetfrequency:
| **xl setfrequency <frequency>**
| The *xl setfrequency* command clarifies the intended frequency of the dates read with the :ref:`xl read command <G7RMxlread>`. Note potential ambiguity of quarterly frequencies in particular when dates are specified in the Excel date format; the dates probably were recorded as the first day of the first month of the quarter. The *frequency* argument should be '1' for annual, '2' for semiannual, '4' for quarterly, or '12' for monthly data frequencies.
.. _G7RMxlmatread:
| **xl matread c(column index grp) r(row index grp) <matname> c(column index grp) r(row index grp) <date>**
| The *xl matread* command reads a data matrix from a spreadsheet and records it as a matrix in a Vam file. Use the first "c" and "r" expressions to indicate the numbers of the columns and rows in which the matrix is contained in the worksheet. Then give the name of the matrix where you want to place the data, and then the column and row group expressions where you want to put the data.
.. _G7RMxlmissing:
| **xl missing [ symbol ]**
| This sets missing value symbols for the spreadsheet. When *G7* is reading the spreadsheet file, a "missing value" entry is recorded in the *G7* data bank for any spreadsheet cell containing this symbol. The symbol may be a word, number, or a string, where strings must be specified in quotes in the *xl missing* command. Up to 10 missing value codes may be stored, but each must be entered separately. If the command is given without arguments, then previous entries are reported. For example, the command allows *G7* to recognize 0.0, NA, and \_N/A\_ as missing value codes when they are read from a spreadsheet file. See also the *xl replace* command.
.. _G7RMxlprintmissing:
| **xl print missing "<value>"**
| This command provides a character or string to represent a missing value when *G7* writes data to a spreadsheet. ::
xl print missing "N/A"
.. _G7RMxlclearmissing:
| **xl clear missing**
| This command clears missing value codes specified in *xl missing*. It also resets the replacement value to the default setting, where replacement values are specified with the *xl replace* command.
.. _G7RMxlreplace:
**xl replace <value>**
This command sets a replacement value for missing values in the spreadsheet file. If *G7* reads a cell that matches an entry set with *xl missing*, then the value is replaced by value. By default, this replacement value is the *G7* missing value code (-0.0000001). <value> must be a number. See also the *xl missing* command.
.. _G7RMxlvisible:
| **xl visible**
| **xl invisible**
| This command makes visible (invisible) the Excel program, provided that Excel is running. Making Excel visible (invisible) may decrease (increase) the execution speed of your script. By default, the Excel window is not visible when *G7* launches the Excel server and opens a spreadsheet file.
.. _G7RMxlNameWorksheet:
| **xl name worksheet <sheetname>**
| This command names the worksheet that currently is open.
.. _G7RMxlcolumn:
| **xl column width <column> <width>**
| This command sets the column <column> in the selected worksheet to width <width>.
| **xl column delete <column>**
| This command deletes the column <column> in the selected worksheet.
.. _G7RMxlrow:
| **xl row height <row> <height>**
| The *xl row* command controls the specified row. Current controls include specification of the row height, given as an scalar.
| **xl row delete <row>**
| This command deletes the row <row> in the selected worksheet.
.. _G7RMxlclose:
| **xl close**
| This command closes the Excel file.
.. _G7RMxlexit:
| **xl exit**
| This command closes an open workbook and disconnects *G7* from the Excel server that is running in the background. If the Excel server was started by *G7*, then it will be stopped. Otherwise, the Excel server may continue running in the background; it can be closed by opening the Task Manager, selecting "Excel," and terminating the process.
**Deprecated Features**
.. _G7RMxlmkseries:
| **xl mkseries <frequency> [text] <column> <row> [text]**
| Read a text string from a worksheet cell, and optionally attach additional text to the string. A new series with the created name is added to the workspace. If a following *xl read* command is given, and if the series name is omitted in the read command, then the series created by the previous *xl mkseries* command will be assumed.