Appending date ids - possible?

For questions regarding programming in the EViews programming language.

Moderators: EViews Gareth, EViews Jason, EViews Moderator, EViews Matt

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Appending date ids - possible?

Postby terrya » Fri Apr 14, 2017 6:22 pm

I want to write a programme to retrieve and append two excel files from the internet that have two different but consecutive date ids. Is it possible to append the two date ids to form a single date id? I can download the two files and combine them manually for the analysis but I want to avoid this if possible.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13306
Joined: Tue Sep 16, 2008 5:38 pm

Re: Appending date ids - possible?

Postby EViews Gareth » Fri Apr 14, 2017 6:27 pm

You'll have to be more specific.

But in general, yes it is possible.
Follow us on Twitter @IHSEViews

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Re: Appending date ids - possible?

Postby terrya » Fri Apr 14, 2017 8:36 pm

HI

They're 2 files on the website of the Reserve Bank of NZ (http://www.rbnz.govt.nz/-/media/Reserve ... -daily.xls
and http://www.rbnz.govt.nz/-/media/Reserve ... 9-2013.xls). I want to download the 1999-2013 data and then append the second). I have no problem downloading either using an EViews programme but I want to get the data into a single workfile, including all the analyses I want to do. I want the combined ids to provide a single sequence of dates.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13306
Joined: Tue Sep 16, 2008 5:38 pm

Re: Appending date ids - possible?

Postby EViews Gareth » Fri Apr 14, 2017 9:14 pm

Code: Select all

wfopen(type=excel) http://www.rbnz.govt.nz/-/media/ReserveBank/Files/Statistics/tables/b1/hb1-daily.xls
import(type=excel, resize, mode=u) http://www.rbnz.govt.nz/-/media/ReserveBank/Files/Statistics/tables/b1/hb1-daily-1999-2013.xls
Follow us on Twitter @IHSEViews

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Re: Appending date ids - possible?

Postby terrya » Fri Apr 14, 2017 9:40 pm

Thanks.

As you know, it works. Perhaps others with this dilemma will get this help.

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Re: Appending date ids - possible?

Postby terrya » Thu Dec 28, 2017 4:49 pm

Hi

Since I posted this, the RBNZ has changed to xlsx files. I've tried to modify the procedure suggested above but can get only the data 1999-2013. Is there a way around this?

EViews Jason
EViews Developer
Posts: 870
Joined: Tue Sep 16, 2008 3:50 pm

Re: Appending date ids - possible?

Postby EViews Jason » Mon Jan 08, 2018 5:49 pm

The xlsx file was not created by Excel and parts of it does not match the expected format.

If you resave the hb1-daily.xlsx file using Excel, EViews will read the file correctly.

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Re: Appending date ids - possible?

Postby terrya » Mon Jan 08, 2018 9:31 pm

Thanks that's precisely what I want to avoid. I used to do this.

Anyway, I can get the data via an R script. I want to get the data directly into an Eviews programme since for what I want to do is done far better in Eviews.

Also, I doubt that the files aren't Excel files.

There is a problem with https which I got around before by just dropping the s for the xls files.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13306
Joined: Tue Sep 16, 2008 5:38 pm

Re: Appending date ids - possible?

Postby EViews Gareth » Mon Jan 08, 2018 9:34 pm

Your best bet is to contact the provider and ask them to fix their Excel making process - it has a bug.
Follow us on Twitter @IHSEViews

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Re: Appending date ids - possible?

Postby terrya » Mon Jan 08, 2018 9:58 pm

Thanks. I was intending to ring tomorrow (our time) to ask whether there's a problem with their files. However,as I indicated earlier, there appears to be no problem with reading the files into R. And your procedure with xls files worked really well.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13306
Joined: Tue Sep 16, 2008 5:38 pm

Re: Appending date ids - possible?

Postby EViews Gareth » Mon Jan 08, 2018 10:03 pm

If you're going to speak to them, you can give them precise details.

If you download the two files and .xlsx and rename them to .zip you can then unzip them (.xlsx files are just zip files). Inside the zip folder there is an XL->Worksheets folder.

Download notepad++ (or any other good raw text editor) and open up the sheet1.xml file from both original files.

You'll notice the problem file (hb1-daily) has a single line - the xml data [the part following the <worksheets> tag] is on the same line as the xml specification line.

For the file that isn't a problem (hb1-daily-1999-2013), you'll notice that the <worksheets> tag is below the xml spec line.
Follow us on Twitter @IHSEViews

terrya
Posts: 107
Joined: Wed Aug 26, 2009 2:37 pm

Re: Appending date ids - possible?

Postby terrya » Mon Jan 08, 2018 10:11 pm

Thanks for this. It'll be very helpful as I really don't know anything about the inner workings of excel.

EViews Jason
EViews Developer
Posts: 870
Joined: Tue Sep 16, 2008 3:50 pm

Re: Appending date ids - possible?

Postby EViews Jason » Tue Jan 09, 2018 10:16 am

Actually the entire file being a single line is not the issue (I incorrectly told Gareth this). There are incorrect and missing tag in the worksheet.xml files. For example the data ranges for each sheet were incorrect and the column range for each row were also not to spec. If you like, you can have them contact me jason@eviews.com and I can tell them more specifically what is wrong and which tags we expect that are missing.


Return to “Programming”

Who is online

Users browsing this forum: No registered users and 24 guests