Exporting to Excel
Moderators: EViews Gareth, EViews Jason, EViews Moderator, EViews Matt
Exporting to Excel
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
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
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting to Excel
matrix m = @cov(g)
m.write test.xls
where g is the group.
m.write test.xls
where g is the group.
Follow us on Twitter @IHSEViews
Re: Exporting to Excel
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!
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 Developer
- Posts: 161
- Joined: Wed Sep 17, 2008 10:39 am
Re: Exporting to Excel
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
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
Re: Exporting to Excel
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.
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.
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting to Excel
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.
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.
Re: Exporting to Excel
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
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
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting to Excel
Not easily. You could freeze the matrix into a table, then add the labels to that table, then save the table
Follow us on Twitter @IHSEViews
Re: Exporting to Excel
That should work. How do I do that?
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting to Excel
Use the freeze command to freeze.
Assign to a table with tablename(row,col)=text
Use tablename.save
Assign to a table with tablename(row,col)=text
Use tablename.save
Follow us on Twitter @IHSEViews
Re: Exporting to Excel
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.
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.
Last edited by Sammoland on Wed Apr 13, 2016 7:08 am, edited 2 times in total.
Re: Exporting to Excel
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?
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting to Excel
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
viewtopic.php?f=5&t=1638
Follow us on Twitter @IHSEViews
Who is online
Users browsing this forum: No registered users and 35 guests