Batch import using SQL command

[equis]

I have time series of several hundred stocks stored in a MySQL database. I would like to import the time series data of each stock into an individual series in Eviews. To do this in an efficient way I am looking for some code which can be executed from the command line in Eviews and which would looks something like this:

series1= sqlimport("select ddate, nprice from stocks where ID = 1")
series2= sqlimport("select ddate, nprice from stocks where ID = 2")
... and so on.

Is there a such a command in Eviews which allows to import data in such a way? I had a look at wfopen but instead of doing what I was looking for it just opens the data import dialogue. Instead I am looking for a command which directly executes the SQL querly and imports the data without requiring any further user input.

Thank you very much for your suggestions.

[EViews Chris]

You're on the right track with import.

The details are going to depend on exactly how the MySQL database is set up.

You should probably first make sure that you can get to the data interactively, by (assuming you have an existing workfile) using 'the menu item File...Import...Import From File', then choosing ''ODBC Database" in the file types.

You're going to need an ODBC DSN entry that contains the info necessary for connecting to the MySQL database. If you haven't already set one up, you can set it up from within this series of dialogs.

Now, I'll assume you know the text for a SQL query that will return a two column table with the first column containing the dates and the second column containing the values of a series eg. "select obsdate,value from mytable where seriesid=1"

Then (at least in simple cases) you can import the data into an eviews workfile by a command that looks something like this:

import "mydsn.dsn" "select dateid, value from seriestable where seriesid=1" @rename value firstseriesname

which will import the data into a series called 'firstseriesname'.

You can string together a series of these commands into an EViews program, or you can use loops in the program to repeatedly call minor variations of a command like this.

A lot depends on the details of how the database is set up, what frequency the workfile and the incoming data is in, etc. etc. but hopefully this will get you on the right track.

[equis]

Hi Chris, thank you for your answer.

I think your answer goes into the right direction. However, one issue remains which prevents me from fully automating the data import:
After entering your suggested command in the command prompt, the "ODBC Dsn Read - Step 1 of 1" window pops up where I need to specify the Basic structure, the frequency and the start date of the data.

While this would be OK if did this for only one data series, it does not work for several hundred data series. Is there a way how I can prevent these window from popping up and, if necessary, provide the required input data directly in the command line?

Thank you.

[EViews Chris]

The interactive prompts are largely going to go away if you include the commands in an EViews program file (rather than enter a line into the EViews command window). EViews prompts in interactive mode whenever a command is 'incomplete' (not all options were explicitly specified in the command line). You should use File...New Program... to create a new EViews batch program.

(I notice that some of the ODBC DSN configuration dialogs are popping up even inside a program: this is pretty unusual and is probably because some of those dialogs are put up by Windows in various situations rather than by us directly).

*However* EViews is still going to need enough information for the read to succeed. EViews can automatically figure out a lot of stuff, but if it can't automatically determine things for your particular data, you'll have to provide more hints in the command. If you can currently just click on 'Finish' when the dialogs pop up, and things work ok, then you won't need to do anything extra inside the EViews program. If you are needing to specify additional options during the interactive dialogs before things will work properly, you're going to have to specify the same options within a command inside a program. Let me know if you can't figure out how the dialog fields map into the options of the import command.

Chris

[equis]

Hi Chris, thank you very much.

Using a program really seems like the right way to avoid this problem. When putting the command into a program file I get the following error message in a popup window:

"Incomplete date specification (no start date provided) in "IMPORT "MYDSNFILE.DSN" "SELECT DATE, VALUE FROM MYTABLE WHERE ID=1" @RENAME VALUE FIRSTSERIESNAME"

I had a look at the Eviews documentation, including the Command and Programming Reference but could not find a description of the parameters of the IMPORT command. Could you please let me know the syntax for adding the start date to my Eviews program?

It could be the case that further parameters, apart from the start date, will be required. Is there a full documentation of all parameters that one could enter in the "ODBC Dsn Read - Step 1 of 1" window and how these would translate into command line syntax? If not, it would be great if you could run the code on your side and see if it's only the start date missing and otherwise also let me know the syntax of specifying other parameters that were required. Thanks again for your help.

[EViews Glenn]

import was added to EViews 7.2 so the docs are not in the printed manuals. If you have updated your help system and PDF files they should be there. You can easily find the docs by opening the help system by selecting Help/EViews Help Topics..., selecint the Index tab, then typing in "import". Select the entry for "import" and you should be good to go. I think you are going to want to use the "@freq" specification as described under the Dated Imports section.

[equis]

Thank you Glenn. I have found the documentation of the import command in the Command and Programming Reference PDF file.