Problem with reading Excel file

For questions and discussion related to reading in and working with data.

Problem with reading Excel file

Unread postby PeterF » Fri Oct 08, 2021 11:50 am

Dear Tom,

I need data for gas storage levels in EU countries, which I could retrieve via a URL from an API of the data provider. Unfortunately, the file format is JSON, which RATS cannot read. I have written a small program in R, to download the data into excel files, which then I want to read into RATS for further analysis. However, I run into some difficulties. The preview in the windows explorer shows that the data is fine, including the date column and the headers for storage and capacity. I can also open the file in Excel without a problem. But the Data-Wizard does not read the date column and the headers.

Code: Select all
OPEN DATA "D:\Rats Files\Energy\France Gas Storage.xlsx"
CALENDAR(7) 2011:1:1

At least with this code, RATS reads the data, but does not reverse the order. The most recent data from the file remains the first value of the series, however, it should be the last one. What is going wrong? I have the Excel file attached. BTW, when i tried to write the data from R into a comma separated file, I got error message IO19.

Best regards
France Gas Storage.xlsx
Data file produced with R
(82.82 KiB) Downloaded 132 times
Posts: 46
Joined: Thu Apr 12, 2012 2:03 pm

Re: Problem with reading Excel file

Unread postby TomDoan » Mon Oct 18, 2021 3:46 pm

The original file (from R) uses a feature in the Excel spec which Excel itself never seems to use, which is an "inline string". I assume this was something added to the spec at some point to make it easier for 3rd party software to create modern Excel spreadsheets (the zipped XML format). (Excel itself uses the original method for handling strings which is to use a shared string table---it's a pain in the you-know-where to set up, but can dramatically reduce the size of big spreadsheets).

Offhand, I'm not seeing where we ever implemented the forced REVERSED order if the file didn't have usable dates. We'll have that and the inline strings implemented in the next maintenance release of RATS. In the meantime, I've attached a RATS format file in the proper order.
France Gas Storage.rat
(64.25 KiB) Downloaded 119 times
Posts: 7236
Joined: Wed Nov 01, 2006 5:36 pm

Re: Problem with reading Excel file

Unread postby willeyt » Thu Dec 30, 2021 11:24 am

I am trying to estimate the impact of the Altman Z score (independent variable) on the Return on Equity (dependent variable) for 262 firms from 2014 to 2020. See the attached Excel file. I have seven years of annual data for each variable. Do I need to transform the data and/or use cross section/panel data? Thanks in advance.
(297.5 KiB) Downloaded 90 times
Posts: 1
Joined: Thu Apr 07, 2011 11:14 am

Return to Data: Reading, Writing, Transforming

Who is online

Users browsing this forum: No registered users and 3 guests