Exporting to Excel

[lbkova]

Hello All,

Could you please help me how to export a Covariance matrix to Excel? I tried to freeze the output from the group of time series in question but what works by the point and click way doesn't seem to work when I try to program it...

Best regards,

Laszlo

[EViews Gareth]

matrix m = @cov(g)
m.write test.xls


where g is the group.

[lbkova]

Thank you Gareth!

Finally I solved the problem this way:

freeze(cov) g.cov
cov.save(t=csv, f, r=a1:f7) %filename

My problem with your solution was that upon opening the Excel file it always returned an error message saying the "Some data maybe lost!" Do you have any idea why that happens?

Another problem of mine is that I cannot really find out how to use the @weekday function. I want to achieve that if @weekday=1 (Monday) then 3 days should be substracted from today's date... So far I have managed to produce something like this:

scalar i =@weekday(@strnow("yyyymmdd"))
if i=1then
%date=@dateadd(@strnow("yyyymmdd"), -3, "dd")
else
%date=@strnow("yyyymmdd")
endif

Thank you for your help!

[EViews Chris]

There's a few problems in your use of date functions.

Firstly, @weekday is an implicit series in the current workfile indicating the weekday number for each row of the workfile. Parentheses following @weekday indicate that a lag to be applied to the implicit series.

You can acheive the result you're looking for with the more general function @datepart(), using "w" as the part of the date you would like to retrieve.

Secondly, there's some issues involving the difference between strings containing dates and numbers containing dates. In most cases you'll want to work with the date numbers until you want a final result in a string. In particular, @now (the date number of today) is generally what you want if you want to perform further manipulation.

Here is a revised version of your example:

scalar i =@datepart(@now, "w")
if i=1then
%date=@datestr(@dateadd(@now, -3, "dd"), "yyyymmdd")
else
%date=@strnow("yyyymmdd")
endif

[flash]

Hello.

On a simpler matter relevant to matrix exporting, is it possible to use an argument to specify in what sheet of an excel file you want your matrix results to be stored? I know that you can do that manually but was wondering if you can program it (couldn't find anything in the user's guide). I use Eviews 6.

Thanks in advance.

[EViews Gareth]

I don't believe so, no

[fmgoto]

I have been using Excel's links to other .xls file. Something like:

1 Read to Eviews from Excel (db.xls)
2 Export from Eviews to Excel (temp.xls)
3 Update links in db.xls from temp.xls (since Eviews writes a temp.xls with no prbolem)

Hope that helps.

[Sammoland]

Hi Gareth

Thank you for the help on this. I ran the correlation matrix variation and exported to excel, but the problem is the names in the matrix do not export. Is there any way to export the variable names along with the values of the correlation matrix to excel?

Thanks in advance

[EViews Gareth]

Not easily. You could freeze the matrix into a table, then add the labels to that table, then save the table

[Sammoland]

That should work. How do I do that?

[EViews Gareth]

Use the freeze command to freeze.

Assign to a table with tablename(row,col)=text

Use tablename.save

[Sammoland]

Thanks! I've tried it but have struggled to freeze the matrix and assign it to a table, but it says "Only strings or numbers can be placed in tables in "Test_T1(2,2)=M1".

This is my code:

group g1 R_SALES_r ABSA_HPI VEH_SALES W_SALES_R FNB_HPI FCP M3 FCH MIN_SALES_EXCLG MIN_SALES

g1.cor

matrix m1= @cor(g1)
freeze m1
test_t1(2,2)=m1
test_t1.save

m1.write {%pathwrite}\correl.xls

*UPDATE* 13/04/2016

I have tried something which has worked, although in a rather rudimentary fashion. Basically it populates the the table with references from matrix:

matrix m1= @cor(g1)
'freeze m1

test_t1(2,2)=m1(1,1)
test_t1(3,2)=m1(1,2)
test_t1(4,2)=m1(1,3)
test_t1(5,2)=m1(1,4)
test_t1(6,2)=m1(1,5)
test_t1(7,2)=m1(1,6)
test_t1(8,2)=m1(1,7)
test_t1(9,2)=m1(1,8)
test_t1(10,2)=m1(1,9)
test_t1(11,2)=m1(1,10)

This works, but it takes quite a while to type out. Is there a simpler way to achieve this? I would like to expand this for a large dataset, which would take a lot of manual typing out.

[Sammoland]

Ah, I have figured it out, thanks. Is there a way to calculate the top ten most correlated variables for a certain variables, then separate them into a group and perform calculation on them? Can this be done in eViews?

[EViews Gareth]

Yes, but you'd have to write the program to do it yourself. It shouldn't be too bad.

viewtopic.php?f=5&t=1638