My Database construction and update question
Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator
My Database construction and update question
Hi everybody.
I am a new user of Eviews 7. Previously used R and Matlab a lot but switched to Eviews now for my job. Right now I am building a Eviewdatabase
for other collegues to access. I want to ask some questions about the structure and function of such database.
(1) Suppose my data source is from datastream via DDE connection with Eviews. I want to save some series in a Eviews database.
What I did is that I got my series using easy query in my workfile, and save to DB. But how can I update these series in
my Eviews database everytime before exporting them to my workfile next time? should I use link function or db registry when I add series to DB( I actually tried both db registry and alias both not working) ?
There got to be a easy way to update all series in my db.
(2) My Eivews database will consist both series from DDE via other databases like datastream, and series from desktop excel files. Is there
a way I can update all these series together? or I have to update them depending on their source?
(3) I will do a lot of query in the future to the Eviews database that I am constructing, is there anything I should have in mind before constructing it?
is the structure of eviews database simply a folder of different series? (of course the series have their own names and descriptions etc.)
Thank you very much if you could answer any of my questions.
I am a new user of Eviews 7. Previously used R and Matlab a lot but switched to Eviews now for my job. Right now I am building a Eviewdatabase
for other collegues to access. I want to ask some questions about the structure and function of such database.
(1) Suppose my data source is from datastream via DDE connection with Eviews. I want to save some series in a Eviews database.
What I did is that I got my series using easy query in my workfile, and save to DB. But how can I update these series in
my Eviews database everytime before exporting them to my workfile next time? should I use link function or db registry when I add series to DB( I actually tried both db registry and alias both not working) ?
There got to be a easy way to update all series in my db.
(2) My Eivews database will consist both series from DDE via other databases like datastream, and series from desktop excel files. Is there
a way I can update all these series together? or I have to update them depending on their source?
(3) I will do a lot of query in the future to the Eviews database that I am constructing, is there anything I should have in mind before constructing it?
is the structure of eviews database simply a folder of different series? (of course the series have their own names and descriptions etc.)
Thank you very much if you could answer any of my questions.
Re: My Database construction and update question
I got part of the answers myself, after playing with it for a while and checking the ref. book.
My idea of updating a database now is to write a code that inports series, rename them, transform them and so on. So that the database is literally a code rather than real data stored there. If this is the only solution, it is acceptable.
However, I have a question about fetching series from an external database such as fred which is built in to connect with Eview.
I gave fred an alias "fred" and try to import series like
fetch fred::gdpa mydatabase::gdp1
But the program can not find the pathway to get gdpa. If anyone could tell me how to let Eviews find fred, that would be really appreciated.
My idea of updating a database now is to write a code that inports series, rename them, transform them and so on. So that the database is literally a code rather than real data stored there. If this is the only solution, it is acceptable.
However, I have a question about fetching series from an external database such as fred which is built in to connect with Eview.
I gave fred an alias "fred" and try to import series like
fetch fred::gdpa mydatabase::gdp1
But the program can not find the pathway to get gdpa. If anyone could tell me how to let Eviews find fred, that would be really appreciated.
-
- EViews Developer
- Posts: 870
- Joined: Tue Sep 16, 2008 3:50 pm
Re: My Database construction and update question
Instead of copying series from an external database to a workfile and then storing theseries to a database, you can copy the series from one database to another database directly using the 'copy' command. For example
You could then run this program anytime you wish to update your database.
Series in a excel files however must be imported into a workfile first and then copied to the database.
In regards to your second post:
'fetch fred::gdpa mydatabase::gdp1 ' should work if 'fred' is an alias in the database registry.
If it is in your database registry what is the exact error message you are receiving?
Code: Select all
'assuming datastream is already in the database registry and it's alias is 'datastream'
copy datastream::gdp mydb::* 'copy series gdp from Datastream to mydb.edb
copy myotherdb::unemp mydb::* 'copy series unemp from myotherdb.edb to mydb.edb
You could then run this program anytime you wish to update your database.
Series in a excel files however must be imported into a workfile first and then copied to the database.
Code: Select all
import myexcelfile.xls
copy myseries mydb::*
In regards to your second post:
'fetch fred::gdpa mydatabase::gdp1 ' should work if 'fred' is an alias in the database registry.
If it is in your database registry what is the exact error message you are receiving?
Re: My Database construction and update question
Thank you Jason first for your reply.
The error message is ’ database not found: the db file "c:\users....db0616.edb" does not exist.’ (db0616 is my target db.)
I think I need to change the path that Eviews uses to search the db.
Also, regarding your code : copy myseries mydb::* , here myseries you mean a specific series right? (if I want to copy all series at the same time it got to be copy * mydatabase:: ?)
Another quick question is: when importing series from excel to local eviews database, is it true that eviews by default recognize the top cell in the column as the name of
the series? (At lease that’s what I found.) Is there a way to change this default setup?
btw, I find out one thing: when I write import [path] mydb in the command window, Eviews pops out a window and ask me to press finish to proceed, but if I add the import line in a program(say import.pgr which is saved on the desktop) and run c:\user\desktop\import.pgr in the command window, Eviews simply imported the series without asking me anything. Very interesting.
Thanks again for your kind help.
The error message is ’ database not found: the db file "c:\users....db0616.edb" does not exist.’ (db0616 is my target db.)
I think I need to change the path that Eviews uses to search the db.
Also, regarding your code : copy myseries mydb::* , here myseries you mean a specific series right? (if I want to copy all series at the same time it got to be copy * mydatabase:: ?)
Another quick question is: when importing series from excel to local eviews database, is it true that eviews by default recognize the top cell in the column as the name of
the series? (At lease that’s what I found.) Is there a way to change this default setup?
btw, I find out one thing: when I write import [path] mydb in the command window, Eviews pops out a window and ask me to press finish to proceed, but if I add the import line in a program(say import.pgr which is saved on the desktop) and run c:\user\desktop\import.pgr in the command window, Eviews simply imported the series without asking me anything. Very interesting.
Thanks again for your kind help.
-
- EViews Developer
- Posts: 870
- Joined: Tue Sep 16, 2008 3:50 pm
Re: My Database construction and update question
1)
- You can either include the path when specifying the destination database or you can add that database to registry and use its alias name
2)You could use
NOTE: this copies everything (series and non-series objects) in the workfile to database. To copy just series objects you will need to use the @wlookup command to construct the list of series objects and then 'copy' each object individually via a loop.
3)Prior to importing, EViews analyzes the excel file and makes its best guess as to where the data starts and ends, the series names, and other information. For simple excel files, EViews will analyze correctly. For the files where it's not clear, EViews may get it wrong, so it depends on the file.
When using the interface, you can adjust the setup interactively. For use in a program, refer to the documentation for the 'import' command. The arguments you are proabably most interested in, can be found in the description for source_description in the syntax section.
4)When executing a command in a program, EViews normally does not ask for any additional information via dialog not already specified in the command. If appropriate, it will use any default settings. A command executed in the command window normally does the opposite and will always display the corresponding dialog, so that you may verify or modify all the settings.
NOTE: If desired, for most commands you can force the corresponding dialog to displayed when executed from a program via the 'prompt' option. Depending on the command however any additional arguments or options may be ignored. In the case of the 'import' command this is true. For example,
will display the import dialog but ignore the file name and the colhead option. You will therefore need to re-enter the file name and colhead setting in the dialogs.
The error message is ’ database not found: the db file "c:\users....db0616.edb" does not exist.’ (db0616 is my target db.)
I think I need to change the path that Eviews uses to search the db.
- You can either include the path when specifying the destination database or you can add that database to registry and use its alias name
Code: Select all
copy series01 c:\temp\mydb::* 'this assumes the database 'mydb' already exist
'or
copy series01 mydbalias::*
2)You could use
Code: Select all
copy * mydatabase::* 'copy all the objects in the workfile to mydatabase
NOTE: this copies everything (series and non-series objects) in the workfile to database. To copy just series objects you will need to use the @wlookup command to construct the list of series objects and then 'copy' each object individually via a loop.
3)Prior to importing, EViews analyzes the excel file and makes its best guess as to where the data starts and ends, the series names, and other information. For simple excel files, EViews will analyze correctly. For the files where it's not clear, EViews may get it wrong, so it depends on the file.
When using the interface, you can adjust the setup interactively. For use in a program, refer to the documentation for the 'import' command. The arguments you are proabably most interested in, can be found in the description for source_description in the syntax section.
4)When executing a command in a program, EViews normally does not ask for any additional information via dialog not already specified in the command. If appropriate, it will use any default settings. A command executed in the command window normally does the opposite and will always display the corresponding dialog, so that you may verify or modify all the settings.
NOTE: If desired, for most commands you can force the corresponding dialog to displayed when executed from a program via the 'prompt' option. Depending on the command however any additional arguments or options may be ignored. In the case of the 'import' command this is true. For example,
Code: Select all
import(prompt) myfile.xls colhead=2
will display the import dialog but ignore the file name and the colhead option. You will therefore need to re-enter the file name and colhead setting in the dialogs.
Re: My Database construction and update question
Thanks Jason so much for your instructions. It would have taken me years to figure out some of your commands.
Especially the default difference between command and program.
I used both copy db1:: series1 db2:: series2 and a loop command to import data. However, both looks slow to me since everytime, they visit the server of foreign database once to pick up one series and rename it. Especially looping method, it takes double the time to process. Is there is quick way to import series so that the program can visit foreign db only once and extract all the series at the same time? it makes big difference to me since I will have over 25,000 series to update on a daily basis.
Also, is there a command line I can use to change the description ( or add my pernalized note) to a series when it's imported?
I used the command copy db1:: series1 db2:: series2(description="my notes here"). the program went through but built in decription of such series was not changed.
P.S. my loop is:
for %se1 %se2 se1 se2 se4 se4 ...
copy db1:: oldname{%se1} db2::newname{%se2}
next
Especially the default difference between command and program.
I used both copy db1:: series1 db2:: series2 and a loop command to import data. However, both looks slow to me since everytime, they visit the server of foreign database once to pick up one series and rename it. Especially looping method, it takes double the time to process. Is there is quick way to import series so that the program can visit foreign db only once and extract all the series at the same time? it makes big difference to me since I will have over 25,000 series to update on a daily basis.
Also, is there a command line I can use to change the description ( or add my pernalized note) to a series when it's imported?
I used the command copy db1:: series1 db2:: series2(description="my notes here"). the program went through but built in decription of such series was not changed.
P.S. my loop is:
for %se1 %se2 se1 se2 se4 se4 ...
copy db1:: oldname{%se1} db2::newname{%se2}
next
-
- EViews Developer
- Posts: 870
- Joined: Tue Sep 16, 2008 3:50 pm
Re: My Database construction and update question
All fetches/copies from a database are done 1 series at a time. So you cannot fetch a specified set of series in 1 swoop. Fetching from a non local database also decreases performance.
Fetching from within a loop however should not decrease performance. The example loop you posted looks ok but since you did not post your entire program, it may be the reason for the decrease in speed. Here is a simpler version if you do not rename the incoming series.
You can change the description of series or any object while in a workfile but you cannot for an object in a database. In the workfile case use
Fetching from within a loop however should not decrease performance. The example loop you posted looks ok but since you did not post your entire program, it may be the reason for the decrease in speed. Here is a simpler version if you do not rename the incoming series.
Code: Select all
%myseries = "series01 series02 series03" ' list of series names to be fetched
for %j {%myseries}
copy db1::{%j} db2::* ' copy db1::series01 to db2::series01, db1::series02 to db2::series02, ....
next
You can change the description of series or any object while in a workfile but you cannot for an object in a database. In the workfile case use
Code: Select all
myseries.label(d) here is my new description
Re: My Database construction and update question
Thanks Jason for your reply.
1. My loop program looks like this:
for %ctry %nctr astr at aust au argt ag belg bt dnmk dk find fi frnc fr germ de gdee gr hgkg hk eire ir ital it jpan jp luxb lu _
neth nl nzea nz pord pt sing sg span es swdn se
copy datastream::ms{%ctry}l z:\loopdatabase.edb::ms{%ctry}l
rename z:\loopdatabase.edb::ms{%ctry}l z:\loopdatabase.edb::ms{%nctr}l
next
The idea is that in old database the series name is msastrl, msaustl etc, I want to rename them in my new database as msatl, msaul etc.
It works slower than simply copy and rename and obviously your looping method works perfectly too. Thanks for that.
2.
I tried the label command by using the program below. (suppose I have series called spmsat, spmsau, spmsar.
You are right in that I have no way to render remarks or give descriptions to any series in a database, what I did is that I import data
from foreign db to eviews db(or to a wf directly) and then fetch them to a workfile , label them and store them back to eview db. From
your last answer it seems the only way I can change the series description. And interestingly, Eviews allows me to even display variable
vaules in the remark or description line! (the second last line of my program below.) This is very important to my work.
3. I actually want to confirm one thing: when I import series to a wf (let's call it wf1), all the series are unified to one frequency and one
date range. After I store these series to eviews db, I believe these series are stored at wf1's freqnency and date range(first and last date) instead
of what frequency/date range each of them originally has right? at least this is what I observed. The reason I ask is that I don't want my method of
import/relabeling to cause any loss of information of my original series. By double clicking a series in a eviews db , I want to window to show the
original information of a series instead of what wf1 has. But it looks not so possible. Is there a way to do that?
Thank you so much again for your reply. I like Eivews more and more now.
My labelling code is as below.
copy datastream::msastrl z:\msci.edb::spmsat
copy datastream::msaustl z:\msci.edb::spmsau
copy datastream::msargtl z:\msci.edb::spmsar
copy datastream::msbelgl z:\msci.edb::spmsbe
copy datastream::msdnmkl z:\msci.edb::spmsdk
fetch(d=z:\msci.edb) spmsat spmsau spmsar spmsbe spmsdk
spmsat.label(r) newlabel for at
spmsau.label(d) newdescriptioin for au
spmsar.label(u) non us dollar
!last=@last(spmsar)
spmsar.label(r) !last
store(d=z:\msci.edb) spm*
1. My loop program looks like this:
for %ctry %nctr astr at aust au argt ag belg bt dnmk dk find fi frnc fr germ de gdee gr hgkg hk eire ir ital it jpan jp luxb lu _
neth nl nzea nz pord pt sing sg span es swdn se
copy datastream::ms{%ctry}l z:\loopdatabase.edb::ms{%ctry}l
rename z:\loopdatabase.edb::ms{%ctry}l z:\loopdatabase.edb::ms{%nctr}l
next
The idea is that in old database the series name is msastrl, msaustl etc, I want to rename them in my new database as msatl, msaul etc.
It works slower than simply copy and rename and obviously your looping method works perfectly too. Thanks for that.
2.
I tried the label command by using the program below. (suppose I have series called spmsat, spmsau, spmsar.
You are right in that I have no way to render remarks or give descriptions to any series in a database, what I did is that I import data
from foreign db to eviews db(or to a wf directly) and then fetch them to a workfile , label them and store them back to eview db. From
your last answer it seems the only way I can change the series description. And interestingly, Eviews allows me to even display variable
vaules in the remark or description line! (the second last line of my program below.) This is very important to my work.
3. I actually want to confirm one thing: when I import series to a wf (let's call it wf1), all the series are unified to one frequency and one
date range. After I store these series to eviews db, I believe these series are stored at wf1's freqnency and date range(first and last date) instead
of what frequency/date range each of them originally has right? at least this is what I observed. The reason I ask is that I don't want my method of
import/relabeling to cause any loss of information of my original series. By double clicking a series in a eviews db , I want to window to show the
original information of a series instead of what wf1 has. But it looks not so possible. Is there a way to do that?
Thank you so much again for your reply. I like Eivews more and more now.
My labelling code is as below.
copy datastream::msastrl z:\msci.edb::spmsat
copy datastream::msaustl z:\msci.edb::spmsau
copy datastream::msargtl z:\msci.edb::spmsar
copy datastream::msbelgl z:\msci.edb::spmsbe
copy datastream::msdnmkl z:\msci.edb::spmsdk
fetch(d=z:\msci.edb) spmsat spmsau spmsar spmsbe spmsdk
spmsat.label(r) newlabel for at
spmsau.label(d) newdescriptioin for au
spmsar.label(u) non us dollar
!last=@last(spmsar)
spmsar.label(r) !last
store(d=z:\msci.edb) spm*
-
- EViews Developer
- Posts: 870
- Joined: Tue Sep 16, 2008 3:50 pm
Re: My Database construction and update question
3)To prevent the lose or change of data, the imported series must be in the same frequency as the workfile. If you have series of different frequency, first group the series by frequency and then re-label each group one frequency at a time. For example, create a weekly workfile, import all of your weekly series into the workfile, re-label the series, and store the series back into the database. Repeat the same process for the other frequencies.
Re: My Database construction and update question
Thanks Jason very much for your suggestion.
Yes I labeled my series in a workfile and then store them in db. It kind of solves the problem.
However, I realized one thing about @name string variable: I want to include the old series name as part of the remark of such series before renaming it. so I wrote
wfcreate(wf=daily) d5 12/31/1969 12/31/2010 # open a workfile
copy database::msastrl # obtain original series from foreign db.
!x=@name(msastrl)
!y=@last(msastrl)
msastrl.label(r) !x !y Austria # include the last data point and original series name as part of the remark.
Rename msastrl newname #rename the series.
the @last works with no problem. but when I use @name, the error message shows that "@name is illegal or a reserved name." Why Eviews does not allow me to use a variable to represent @name??
My goal is: for all the series in the wf, include their original name as part of their remarks.
I also used a loop but not working either.
for %series myworkfile::*
%x=@name(%series)
%series.label(r) %x
next
and it doesn't work. Did I use the string variable in a wrong way, or there are certain perhibitions using @name?
Thanks again Jason.
Yes I labeled my series in a workfile and then store them in db. It kind of solves the problem.
However, I realized one thing about @name string variable: I want to include the old series name as part of the remark of such series before renaming it. so I wrote
wfcreate(wf=daily) d5 12/31/1969 12/31/2010 # open a workfile
copy database::msastrl # obtain original series from foreign db.
!x=@name(msastrl)
!y=@last(msastrl)
msastrl.label(r) !x !y Austria # include the last data point and original series name as part of the remark.
Rename msastrl newname #rename the series.
the @last works with no problem. but when I use @name, the error message shows that "@name is illegal or a reserved name." Why Eviews does not allow me to use a variable to represent @name??
My goal is: for all the series in the wf, include their original name as part of their remarks.
I also used a loop but not working either.
for %series myworkfile::*
%x=@name(%series)
%series.label(r) %x
next
and it doesn't work. Did I use the string variable in a wrong way, or there are certain perhibitions using @name?
Thanks again Jason.
-
- EViews Developer
- Posts: 870
- Joined: Tue Sep 16, 2008 3:50 pm
Re: My Database construction and update question
Instead of
use
Similarly, use (EDIT: Do not use, see below)
Code: Select all
!x=@name(msastrl)
!y=@last(msastrl)
msastrl.label(r) !x !y Austria # include the last data point and original series name as part of the remark.
use
Code: Select all
%x=msastrl.@name
!y=@last(msastrl)
msastrl.label(r) %x !y Austria # include the last data point and original series name as part of the remark.
Similarly, use (EDIT: Do not use, see below)
Code: Select all
for %series myworkfile::*
%x=%series.@name
%series.label(r) %x
next
Re: My Database construction and update question
Jason I tried your code for including series name to the description, but the error message pops out saying that series.@name is not defined or reserved. I will try again.
I use excel to upload series to workfile and then to eviews db. The structure of excel file looks like:
Name US UK EA
Code USOL0958R UKOL0958R EAOL0958R
31/01/1970 96.7115 96.704 100.85
27/02/1970 96.0483 96.9597 100.3424
31/03/1970 95.4941 97.1725 99.8556
30/04/1970 95.0747 97.2849 99.3849
... ...
The command I use is: wfopen(page=daily) "y:\excel1.xlsm" colhead=2, range="sheet1" namepos=last
However, Eviews recognize the first column as a series rather than date for each observations. Consequently a series called "code" was included in the wf. And all other series in the wf
are recognized as undated series.
In order to solve this problem I add date description by adding either @date code or @data(code) to the end of the above command. Neither works.
Can you tell me a way I can link each series (such as usol0958r) in the table to the date series? Thank you in advance.
I use excel to upload series to workfile and then to eviews db. The structure of excel file looks like:
Name US UK EA
Code USOL0958R UKOL0958R EAOL0958R
31/01/1970 96.7115 96.704 100.85
27/02/1970 96.0483 96.9597 100.3424
31/03/1970 95.4941 97.1725 99.8556
30/04/1970 95.0747 97.2849 99.3849
... ...
The command I use is: wfopen(page=daily) "y:\excel1.xlsm" colhead=2, range="sheet1" namepos=last
However, Eviews recognize the first column as a series rather than date for each observations. Consequently a series called "code" was included in the wf. And all other series in the wf
are recognized as undated series.
In order to solve this problem I add date description by adding either @date code or @data(code) to the end of the above command. Neither works.
Can you tell me a way I can link each series (such as usol0958r) in the table to the date series? Thank you in advance.
-
- EViews Developer
- Posts: 870
- Joined: Tue Sep 16, 2008 3:50 pm
Re: My Database construction and update question
1)The code I posted is not correct.
It should read
2)EViews is reading the data you posted correctly. I used the same command you posted and a monthly workfile that contains the 3 series was created. I suspect you may have a missing or invalid date in the 'code' series and therefore EViews believes it is not a series of dates.
Code: Select all
for %series myworkfile::*
%x=%series.@name
%series.label(r) %x
next
It should read
Code: Select all
%object_list = @wlookup("myworkfile::*") 'get a list of object names in myworkfile
for !i = 1 to @wcount(%object_list) ' loop through the objects
%obj = @word( %object_list, !i) ' get the name of an object
{%obj}.label(r) %obj ' set the remark for the object
next
2)EViews is reading the data you posted correctly. I used the same command you posted and a monthly workfile that contains the 3 series was created. I suspect you may have a missing or invalid date in the 'code' series and therefore EViews believes it is not a series of dates.
Who is online
Users browsing this forum: No registered users and 15 guests