Open Excel file very long with wfsave

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

Bob
Posts: 17
Joined: Mon Jan 17, 2022 6:10 am

Open Excel file very long with wfsave

Postby Bob » Sat Jan 22, 2022 2:41 am

Hello

My Eviews program imports data from a closed Excel file ("import" command) then performs sequential calculations under Eviews and exports the results to another open Excel file ("wfsave" command).

The execution time is long (5 hours and 30 minutes) given the volume of informationprocessed.

However, a few times, the program was fully executed in less than 20 minutes!

I noticed that it was when running wfsave that the processing time could change (the mention "open Excel file" at the bottom left of the Eviews screen remaining displayed more than 5 seconds in case the program goes slowly and is almost instantaneous otherwise).

Note: the computer has just been remastered (everything is clean) there is for the moment only Windows defender.

Do you have an idea?

A big thank you in advance to the whole team :)

Bob

Technical characteristics:

DELL Computer, Intel(R) Core(TM) i9-10885H CPU @ 2.40GHz 2.40 GHz processor
64.0 GB installed RAM (63.8 GB usable)
64-bit operating system, x64 processor
Windows 10 ProEdition, version 21H2
Microsoft Excel for Microsoft 365 MSO (Version 2112 Build 16.0.14729.20254) 64-bit
Eviews 12 Enterprise Edition 64-bit

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

Re: Open Excel file very long with wfsave

Postby EViews Gareth » Sat Jan 22, 2022 9:00 am

How many times are you doing wfsave?
Follow us on Twitter @IHSEViews

Bob
Posts: 17
Joined: Mon Jan 17, 2022 6:10 am

Re: Open Excel file very long with wfsave

Postby Bob » Sun Jan 23, 2022 3:01 am

2200 wfsave (principalement des séries chronologiques annuelles 1980-2021)

remarques :

Avec Excel uniquement, mon fichier de 30 Mo s'ouvre en 8 secondes environ.

Avec Eviews, l'écriture dans Excel avec wfsave utilise presque 5 heures (2200x8s) puis il y a aussi l'importation de données et leur traitement.

C'est comme si à chaque écriture (utilisation de wfsave) Eviews ouvrait mon gros fichier Excel alors qu'il est déjà ouvert.

Ce qui est étonnant, c'est qu'il est arrivé que le programme Eviews tourne en mois de 20mn !!! d'où ma frustration :)


PS : Je double ce post sur support@eviews.com avec mon numéro de License Eviews.

Bonne journée

Bob

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

Re: Open Excel file very long with wfsave

Postby EViews Gareth » Sun Jan 23, 2022 5:19 pm

The first time you save to the file in the program, use the option

Code: Select all

wfsave(mode=update_hold)

instead of mode=update (which is what I presume you are using currently).

Just the first time, after that you can use mode=update.

Then the last time use mode=update_release.
Follow us on Twitter @IHSEViews

Bob
Posts: 17
Joined: Mon Jan 17, 2022 6:10 am

Re: Open Excel file very long with wfsave

Postby Bob » Tue Jan 25, 2022 2:31 am

Bonjour,

Merci pour l'idée mais mon programme ne fonctionne pas plus rapidement !

voilà comment j'ai traduit votre message :

1er wfsave :
wfsave(type=excelxml, mode=update_hold, noid) {chemin_fichier_mod} range="MDE!h7" byrow @keep pibvolp_mde_fmi @smpl @first {date_fin_obs}

2ème wfsave et suivants :
wfsave(type=excelxml, mode=update,noid) {chemin_fichier_mod} range="MDE!h14" byrow @keep pibdeflp_mde_fmi @smpl @all
...

Dernier wfsave :
wfsave(type=excelxml, mode=update_release,noid) {chemin_fichier_mod} range="MDE!h78" byrow @keep mscipr_mde_eur_vol1a @smpl @all

Bob
Posts: 17
Joined: Mon Jan 17, 2022 6:10 am

Re: Open Excel file very long with wfsave

Postby Bob » Tue Jan 25, 2022 7:40 am

Bonjour,

Voici le code de mes derniers tests

wfcreate(wf=test2,page=AN) A 1980 2025
string chemin_fichier_mod = "C:\AMF\Analyse-Macro-Financiere-mod.xlsm" ' fichier de 27,4 Mo
string chemin_fichier_data = "C:\AMF\Analyse-Macro-Financiere-data.xlsx" ' fichier de 28,6 Mo
string m
m="2025"

tic
import(page=an) {chemin_fichier_data} range=a_MDE!$E$17:$E$71 colhead=8 namepos=first na="#N/A" @freq A 1980 @smpl @first {m}
scalar temps_read=@toc ' entre 1s à 5 secondes, selon les essais, pour lire 1 série chronologique annuelle sur 1980-2026 (fichier excel fermé --> decompression et lecture ; à noter : c'est plus long avec le fichier excel ouvert !)
toc

tic
pageselect AN
'wfsave(type=excelxml, mode=update_host, noid) {chemin_fichier_mod} range="MDE!h8" byrow @keep pibvolp_mde_fmi @smpl @all (test solution EViews Gareth)
wfsave(type=excelxml, mode=update, noid) {chemin_fichier_mod} range="MDE!h8" byrow @keep pibvolp_mde_fmi @smpl @all
'wfsave(type=excelxml, mode=update_release, noid) {chemin_fichier_mod} range="MDE!h8" byrow @keep pibvolp_mde_fmi @smpl @all (test solution EViews Gareth)
!elapsed = @toc
scalar temps_write=@toc ' entre 11 et 13 secondes selon les essais (ou exceptionellement 0,5 seconde !) pour écrire 1 série chronologique sur 1980-2026 (fichier excel ouvert --> openning excel file, scanning for data...)
toc

' Remarques :
' wfsave a mis 0,5 seconde que quelques fois après un "nettoyage de disque windows" mais aussi après avoir décoché "compile to intel machine code" sans que cela soit reproductible !!!
' Il me semble qu'il y ait un problème de cache ???

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

Re: Open Excel file very long with wfsave

Postby EViews Gareth » Tue Jan 25, 2022 8:33 am

I'm afraid we don't have much alternative right now. The next version of EViews, EViews 13 will have a faster Excel writing engine (that doesn't use Excel!).
Follow us on Twitter @IHSEViews

Bob
Posts: 17
Joined: Mon Jan 17, 2022 6:10 am

Re: Open Excel file very long with wfsave

Postby Bob » Tue Jan 25, 2022 8:40 am

Ok merci. Quel est la date de sortie d'Eviews 13 ?

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

Re: Open Excel file very long with wfsave

Postby EViews Gareth » Tue Jan 25, 2022 9:15 am

First half of this year
Follow us on Twitter @IHSEViews


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 9 guests