Grootste bottleneck Excel

[SamPep]

Mijn vraag
Ik ben voor mijn scriptie bezig met excel en VBA en gebruik dus bestanden die groter zijn dan 100mb. Nu heb ik imo degelijke hardware, maar ik vroeg me af wat ik kan gebruiken om excel sneller te laten lopen, of dat het puur aan excel ligt.

Heb momenteel een 6600k met 8gb 3200 ram m.2 ssd en gtx 1060. Loopt het sneller als ik mijn ram zou upgraden?

[Slavy]

Zijn het .XLS of .XLSX bestanden? Als je een .XLS omzet naar .XLSX wordt hij al een stuk kleiner en geoptimaliseerd.

Daarnaast, heb je al in Google gekeken?

[Voor 23% gewijzigd door Slavy op 10-10-2018 13:16]

[SamPep]

.XLSM, moet macro enabled zijn, vandaar dat het ook belachelijk grote bestanden zijn.

En meeste internet tips gaan niet verder dan gebruik 64bit versie, enable all cores en schakel macro's uit (die ik juist nodig heb).

[Voor 44% gewijzigd door SamPep op 10-10-2018 13:22]

[F_J_K]

Je vergeet aan te geven wat je zelf ziet als bottlenecks: wat is het geheugengebruik? Wordt er 1 of meer CPU-cores vol bezet?

Er zijn algemene optimalisatietrucjes, zoals uitschakelen van schermupdates, query's, .... Je kunt kijken of de volgorde van acties kan helpen. Maar de beste tip: gebruik een schroevendraaier niet als hamer. Zijn er geen betere analyse- of datamanipulatie-tools beschikbaar?

[McKaamos]

Mag ik vragen wat je aan het doen bent, dat je een Excel-file van 100+ MB hebt?
En is het niet het overwegen waard om zoiets om te zetten naar een database? Om bij Microsoft Office te blijven: Heb je al eens wat met Access gedaan?

En als het dan tóch echt in Excel moet blijven, heb je al eens gekeken naar varianten van andere officepakket leveranceiers? Denk aan OpenOffice Calc of LibreOffice Calc?
In mijn ervaring zijn die een stuk sneller met grote bestanden. En ze bieden functies die Microsoft niet bied, zoals Search&Replace met Regular Expressions (dat is zooooo fijn )

[Voor 45% gewijzigd door McKaamos op 10-10-2018 13:30]

[CappieL]

En hoe zijn de gebruikte macro's geprogrammeerd?
Als je bijvoorbeeld berekeningen, iteraties, layout-technische veranderingen doet, kan het enorm veel tijd (en resources) schelen als je bijvoorbeeld "Application.ScreenUpdating = False" doet ipv "True"

[Slavy]

SamPep schreef op woensdag 10 oktober 2018 @ 13:18:
.XLSM, moet macro enabled zijn, vandaar dat het ook belachelijk grote bestanden zijn.

En meeste internet tips gaan niet verder dan gebruik 64bit versie, enable all cores en schakel macro's uit (die ik juist nodig heb).

En de Microsoft documentatie hierover? Staan genoeg tips die jij niet aan kaart. https://docs.microsoft.co...-performance-obstructions

[begintmeta]

Sowieso kan je je misschien inderdaad even afvragen of Excel/VBA het gesschiktste gereedschap is voor wat je wil doen, op basis van wat je post is dat zo op het eerste gezicht niet evident.

[Voor 16% gewijzigd door begintmeta op 10-10-2018 13:58]

[eric.1]

SamPep schreef op woensdag 10 oktober 2018 @ 13:14:
Loopt het sneller als ik mijn ram zou upgraden?

Ligt eraan wat je naast Excel doet. Maar Excel 64bit gebruikt max 4GB Mem, als je 8GB in je systeem hebt zitten lijkt het me onwaarschijnlijk dat dit de bottleneck is.

Ik denk eerlijk gezegd dat of je VBA zeer inefficient is, of dat Excel niet de juiste tool is voor jouw project.

[Puch-Maxi]

Wat probeer je precies te doen, wat voor data is het? Misschien kun je wat aan pre-processing doen, als het CSV data is bijvoorbeeld in Python.

[SamPep]

Om niet al te diep in mn research method te gaan, ik moet 1000tal regressies doen op een dataset die niet 1:1 gelijk is. Met de programma's waar ik ervaring mee heb (stata, spss, eviews) moet je dit 1 voor 1 doen omdat de dataset niet matched. Dit is met een vba macro op te lossen, waardoor ik aan excel gebonden ben.
Nou weet ik dat R of andere statistische programmeer programma's dit waarschijnlijk beter kunnen doen, maar ik heb 0.0 ervaring met programmeren en de vba macro kostte mij al te lang .

Daarbij ben ik bezig met mutual fund optimization. Van al de regressies heb ik de coefficienten nodig waar ik uiteindelijk een portfolio mee moet maken, waardoor ik sowieso vrij gebonden aan excel ben.

[Voor 16% gewijzigd door SamPep op 10-10-2018 14:20]

[Wim-Bart]

Optimaliseren:
https://plumsolutions.com...ory-and-file-performance/

Vooral dit:
Work from left to right. Excel starts calculating from the top left-hand corner, then continues across and down the sheet. This means that input values should be to the left and above the formulas which are referencing them.

[begintmeta]

SamPep schreef op woensdag 10 oktober 2018 @ 14:13:
Om niet al te diep in mn research method te gaan, ik moet 1000tal regressies doen op een dataset die niet 1:1 gelijk is. Met de programma's waar ik ervaring mee heb (stata, spss, eviews) moet je dit 1 voor 1 doen omdat de dataset niet matched. Dit is met een vba macro op te lossen, waardoor ik aan excel gebonden ben.
Nou weet ik dat R of andere statistische programmeer programma's dit waarschijnlijk beter kunnen doen, maar ik heb 0.0 ervaring met programmeren en de vba macro kostte mij al te lang .

Ik zie niet helemaal voor me wat je precies bedoelt, maar ik vermoed dat je met Stata of SPSS misschien meer had gekund dan je dacht.

[m-vw]

CappieL schreef op woensdag 10 oktober 2018 @ 13:31:
En hoe zijn de gebruikte macro's geprogrammeerd?
Als je bijvoorbeeld berekeningen, iteraties, layout-technische veranderingen doet, kan het enorm veel tijd (en resources) schelen als je bijvoorbeeld "Application.ScreenUpdating = False" doet ipv "True"

Behalve de screenupdating kan je ook denken aan het uitzetten van het automatische herberekenen.
Kan je handmatig doen via Formulas -> Calculation options of in de macro met Application.Calculation = xlManual aan het begin van de macro en Application.Calculation = xlAutomatic aan het eind.

Ik begin zelf vaak met:

Application.ScreenUpdating = False
Application.Calculation = xlManual

En eindig met:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

Een beruchte Excel killer zijn ook de volatile functies zoals indirect(). Die voeren herberekeningen uit bij iedere wijziging in een sheet. Google is hier uw vriend.

[SamPep]

m-vw schreef op woensdag 10 oktober 2018 @ 15:03:
[...]


Behalve de screenupdating kan je ook denken aan het uitzetten van het automatische herberekenen.
Kan je handmatig doen via Formulas -> Calculation options of in de macro met Application.Calculation = xlManual aan het begin van de macro en Application.Calculation = xlAutomatic aan het eind.

Ik begin zelf vaak met:

Application.ScreenUpdating = False
Application.Calculation = xlManual

En eindig met:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

Een beruchte Excel killer zijn ook de volatile functies zoals indirect(). Die voeren herberekeningen uit bij iedere wijziging in een sheet. Google is hier uw vriend.

Thanks, dit scheelt echt enorm!

[SamPep]

begintmeta schreef op woensdag 10 oktober 2018 @ 14:35:
[...]

Ik zie niet helemaal voor me wat je precies bedoelt, maar ik vermoed dat je met Stata of SPSS misschien meer had gekund dan je dacht.

Ik heb stock performance data die ik moet regressen op factor data. Alleen niet elke stock bestaat even lang, waardoor ik per stock handmatig had moeten selecteren tot welke datum ik het wilde regressen. De prof. Die mij begeleid gaf aan dit icm vba te doen want dit is bij stata e.d. erg tedious, aangezien daar geen functie voor is.

[Atanamir]

Je kunt ook nog eens kijken naar het gebruik van arrays ipv for ... next loops. Zeker als je bestand echt tienduizenden rijen heeft merk je dat verschil zeker!
Post in deze thread legt dat wel goed uit.

https://www.mrexcel.com/f...ng-large-amount-data.html

Update: Wellicht obvious, maar let ook op dat zodra je calculation op manual zet, zoals in een post hierboven staan, eventuele formules in je sheet niet meer geupdate worden terwijl de macro loopt.

[Voor 24% gewijzigd door Atanamir op 10-10-2018 17:28]

[Slavy]

SamPep schreef op woensdag 10 oktober 2018 @ 16:24:
[...]


Thanks, dit scheelt echt enorm!

Dit stond ook allemaal in eerder genoemde Microsoft documentatie, tip voor volgende keer dus om die eerst door te lezen.