Problem with reading Excel file

[PeterF]

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
DATA(FORMAT=XLSX,NOLABELS,ORG=COLUMNS,TOP=2,LEFT=2,REVERSE) 2011:01:01 2021:10:06 STORAGE CAPACITY

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
PeterF

[TomDoan]

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.

[willeyt]

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.