*! version 1.1.3 07oct2022

Title

xtimportu -- Import monthly, quarterly, half-yearly, or yearly time
series and panel data as panelvar timevar valuevar from a
supported file format to memory or a file

Syntax

xtimportu
import syntax
[, options]

Import data
* (import options) none or all options from
import

Preformat
preformat(strings:string) any inbuilt and/or user-defined
command with standard Stata syntax, prefix included (omittable)

Process
panelvar(varname:panelvar) unit variable (omittable)

regex(strings:string) regular expressions, separated by "|"
for multiple matches
(omittable)

encode(strings:string) replacement strings
for each match in regex(), separated by "|" (omittable)

timevar(varname:timevar) time variable
for long data, omitted for wide

tformat(strings:string) s2 in -ly()
datetime functions,
for example "M[##]Y" or "[##]YQ" (omittable)

tfrequency(strings:Y|H|Q|M) time frequency
(yearly, half-yearly, quarterly or monthly)

drop drop yearly sums for half-years,
quarters, and months

tdestring return timevar
in a %t format instead of
a string "####[-T#]"

Finalize
generate(strings:string) specify the new name
for the returned valuevar (omittable)

* (destring options) none or all options
from destring

tostring return valuevar
as a string, incompatible with
destring options

Save output
* clear replace data in memory

* export(... [using] filename, ...) export
data to filename, forwarded
to export

* saving([filename] [, ...]) save
data to filename, forwarded
to save (no saveold)

* One or more of clear, export(string asis)
or saving(string asis) is/are required.

Description

xtimportu imports long or wide (pivoted) monthly, quarterly half-yearly
or yearly time series and panel data from a supported file type using
standard Stata import syntax, filtering cross-sectional units with
regular expressions (case-sensitive!) and
encoding the matches (each match to a string,
spaces are replaced with underscores "_") (if required), and returns
long data as panelvar timevar
valuevar to memory and/or to a file using standard Stata
export and/or save
syntax. Imported data are assumed to be wide if
timevar is omitted.

xtimportu uses (SSC) sxpose2 in combination with destring
and reshape to process wide (pivoted) data and accepts all
destring options.

For wide (pivoted) data the first imported row (_n
== 1) must include future timevar values (at least
the years with possible breaks in place of higher frequency) which can be
achieved with the help of import and xtimportu subcommand
options such as
cellrange([start][:end])
or preformat(string).

Unicode strings are supported in both
regex(string)
and encode(string).

Options

* (import options) are none or all options from
import such as
import delimited's
delimiters(), *range(), as*, *quotes(), *rows()
*separator() and parselocale() or
import excel's sheet(),
cellrange(), firstrow, allstring() and locale()
with the exception of varnames(), firstrow, case() and
clear which are handled internally and will be ignored (if typed after
comma in the command).

preformat(strings:string) is an inbuilt command (including do
and run) and/or a user-defined program with standard Stata
syntax (prefix included). Multiple commands (even those containing
mata) can be wrapped into a [P] program
or a DO-file and passed to xtimportu as one
command, for example, ..., preformat(myprogram [args], [options])
or ..., preformat(do filename [args]).

panelvar(varname:panelvar) is the varname of the cross-sectional
unit(s) (one or multiple) or the first variable (column) (if omitted)
in the imported data. If the data lack panelvar, either
a) the use of import instead of xtimportu should be considered
or b) panelvar should be pre-generated, for example,
with the help of preformat(string).

NB The returned panelvar has the name
"unit".

regex(strings:string) is/are regular expression(s), separated by "|"
for multiple matches, and equal(s) "^.*$" if omitted. The user should note
that matching is case-sensitive!

encode(strings:string) is/are string
replacement(s) for regex(string), separated
by "|". The user should note that
spaces in string will be replaced with underscores
"_" (see the example below).

timevar(varname:timevar) is the varname of the date for the imported
long data. The data are assumed to be wide if
timevar(string) is omitted.

NB The returned timevar has the full name
of tfrequency(string): "year",
"halfyear", "quarter" or "month".

tformat(strings:string) is the s2 in
yearly(s1,s2[,Y]), halfyearly(s1,s2[,Y]),
quarterly(s1,s2[,Y]) or monthly(s1,s2[,Y])
datetime functions, for example, "M[##]Y" or
"[##]YQ". If omitted, xtimportu will (re)construct the
timevar automatically,
starting with "####" or "####-T1" (always with the first half-year,
quarter or month in the first year for time frequency higher than yearly) and
dropping yearly sums (for half-years, quarters, and months if such sums
are present in the data and drop is specified).

NB The user should omit this option for unsupported time formats
such as "IV/2020" or "2020年11月"!

tfrequency(strings:M|Q|H|Y) is non-omittable and is required
to pass time frequency (yearly, half-yearly, quarterly or monthly) to the
command.

drop is required to drop yearly sums for half-years, quarters, and months
(if such sums are present in the data).

tdestring is required to return timevar
in a %t format, the default format
is a string "####", "####-H#", "####-Q#" or "####-M#".

generate(strings:string) is required to replace the name of
valuevar.

NB The returned valuevar has the default name
"value".

* (destring options) are none or all options
from destring such as ignore(), float, percent or
dpcomma with the exception of generate(), replace and
force which are handled internally and will be ignored (if typed after
comma in the command).

tostring is required to ignore the default destring and
is incompatible with any
destring options
(if typed after comma in the command).

clear is required to replace the data in memory,
even though the current data have not been saved to disk, otherwise
preserve and restore are used.

export(... [using] filename, ...) is an
everything-like string forwarded
to export. The output file can be
CSV,
Microsoft Excel or any other
supported file type .

saving([filename] [, ...]) is an
everything-like string forwarded
to save (saveold is not supported, the user should use
clear with xtimportu and a separate
saveold command!).

Remarks

xtimportu is not a replacement tool for import and was designed
to help automate data collection! xtimportu is preferable to import
if the data require filtering and/or they are wide (pivoted).

To build a dataset from several hundred CSV, Microsoft Excel or other supported
file types, the user may want to code a loop using xtimportu,
append, merge or frlink - frget. Input files
can be pre-processed with a user-defined program passed to xtimportu
in preformat(string) to ensure same
cellrange([start][:end]) or
other options for each file.

For detailed information on sxpose2, see sxpose2.

Examples

****
* Example 1. Population time series for the Czech Republic
* (a country in Central Europe, EU member since 2004)
****

* RegEx for the indicator, case sensitive!
* unoptimized, illustration only
. local regex "Počet"

* ČSÚ's (Czech Statistical Office) file URL for Population
. local url "https://www.czso.cz/documents/10180/123502877/"
. local url "`url'32018120_0101.xlsx/"
. local url "`url'd60b89c8-980c-4f3a-bc0c-46f38b0b8681"
. local url "`url'?version=1.0"

* import the time series data to memory, unit: thousand
. xtimportu excel "`url'", cellrange(A3) regex(`regex') ///
encode("Czech Republic") tfreq(Y) tde clear

* revert underscores to spaces in the unit
. replace unit = ustrregexra(unit, "_", " ")

* tsset data
. tsset year

****
* Example 2. FDI matrix from UNCTAD's Bilateral FDI statistics
* (historical data, 2000–2014)
****

* RegEx for the EU-28, case sensitive! "{0,}$"
* (0 or more non-word characters) excludes Netherlands Antilles
* unoptimized, illustration only
. local regex "Austria|Belgium|Bulgaria|Croatia|Cyprus|"
. local regex "`regex'Czech Republic|Denmark|Estonia|Finland|"
. local regex "`regex'France|Germany|Greece|Hungary|Ireland|"
. local regex "`regex'Italy|Latvia|Lithuania|Luxembourg|Malta|"
. local regex "`regex'Netherlands\W{0,}$|Poland|"
. local regex "`regex'Portugal|Romania|Slovakia|Slovenia|Spain|"
. local regex "`regex'Sweden|United Kingdom"

* UNCTAD's (United Nations Conference on Trade and Development)
* file URL for the U.S.
. local url "https://unctad.org/system/files/"
. local url "`url'non-official-document/webdiaeia2014d3_USA.xls"

* import the panel data to memory, export a copy as a CSV file
. view browse "`url'"
. xtimportu excel /webdiaeia2014d3_USA.xls, ///
sheet("inflows") cellrange(E5) ///
regex(`regex') tfreq(Y) clear tde ///
export(delimited "./usa_fdi_matrix.csv", replace)

* rename variables to form the 28x1 aka the EU-28 x U.S. FDI matrix,
* unit: million USD
. rename unit from
. rename value to_USA

* xtset data
. encode from, gen(id)
. xtset id year

Author

Ilya Bolotov

Prague University of Economics and Business

Prague, Czech Republic

ilya.bolotov@vse.cz

Thanks for citing this software and my works on the topic:

Bolotov, I. (2020). XTIMPORTU: Stata module to import monthly, quarterly,
half-yearly, or yearly time series and panel data as panelvar timevar
valuevar from a supported file format to memory or a file. Available from
https://ideas.repec.org/c/boc/bocode/s458880.html.