Alternatief voor MEG grote Excel sheets met heel veel VBA

[chicky]

Beste mede tweakers,

Ik ben al ongeveer 2 jaar bezig met een aantal Excel sheetjes die langzaam zijn gegroeid tot wat het nu is.
Ik ga proberen te omschrijven wat ik heb.

Main sheet: Excel sheet met diverse tabbladen waarvan het grootste tabblad ongeveer 16.500 regels en 59 kolommen is en het sheet bevat voornamelijk met platte tekst.

Om een aantal kolommen in de Main sheet te vullen haal ik informatie uit ongeveer 25 externe Excel sheets. In deze externe sheets staan alles bij elkaar circa 16.500 regels, waar er per regel allerlei berekeningen worden uitgevoerd.
De uitkomst van deze berekeningen worden als platte tekst in de main sheet gekopieerd.
Deze 25 externe sheets halen op hun beurt ook weer informatie uit 3 andere sheets.
Helemaal in het begin waren al deze sheets 1 bestand. Toen dat een beetje te groot werd heb ik alles uit elkaar getrokken. Tussen de bestanden onderling zijn er geen cel verwijzingen e.d. vooral om de bestanden werkbaar te houden.

Als de main sheet helemaal is gevuld, itereer ik op een bepaalde manier steeds een bepaald aantal rijen. Deze itratie is niet helemaal recht toe recht aan maar daar zitten allerlei voorwaarden e.d. aan om het gewenste resultaat te krijgen. De resultaten van de itratie worden vervolgens in een nieuw Excel sheet gezet met als resultaat 1 tabbald met bijna 1.000.000 regels en 39 kolommen.
Dit tabblad sorteer ik een aantal keer en "ontdubbel" ik het geheel (met ook weer allerlei voorwaarden) met als eind resultaat een sheet met circa 250.000 regels en 39 kolommen.

Het grootste deel van de bewerkingen en berekeningen doe ik door middel van VBA.
Nu mijn verzameling Excel sheets zo groot wordt en de Excel sheets zelf ook nogal uit hun jasje beginnen te groeien, ondervind ik steeds meer problemen tijdens het draaien van mij VBA macros.
Het vervelende is dat Excel soms op verschillende momenten crasht maar het kan soms ook helemaal foutloos gaan. Als Excel is gecrashed is er geen log of iets dergelijks waar ik uit kan halen waardoor de crash heeft geplaats gevonden.

Ik ben op zoek naar een alternatief voor bovenstaande. Iemand een idee?
Natuurlijk heb ik er over gedacht alles in een database te gooien maar die zijn niet zo geschikt om berekeningen te maken.
Ook heb ik nagedacht om sommige delen in een database te gooien en de delen waarin veel wordt berekend in Excel te houden.
Maar ik zie in de database oplossing nogal wat uitdagingen. Vooral omdat databases niet bedoeld zijn om in te rekenen. (O ja Excel is niet bedoeld om 1.000.000 regels in op te slaan )

Ik ben meer aan het denken in de richting van "platte" databestanden (een soort CSV bestanden) waar ik vervolgens met een stuk extern software tegen aan ga praten en de boel ga bewerken.
Ik zou dit kunnen maken in bijvoorbeeld python, C++, VB of wat nodig is..

En dan nu mijn vraag:
Heeft iemand een zinnig idee waarmee ik een goede oplossing zou kunnen bouwen?

Tot nu toe draaien mijn Excel sheets in een remote omgeving met voldoende resources maar ik kan ze ook lokaal draaien op een i7-4770 met 16 GB.
Ik heb de beschikking over Excel 2016 in zowel een 32 bit als 64 bit uitvoering.
Jammer genoeg doen de problemen zich bij allebei de systemen voor.

[RobIII]

chicky schreef op donderdag 24 oktober 2019 @ 12:26:
Vooral omdat databases niet bedoeld zijn om in te rekenen.

Says who? Het hangt er een beetje van af hoe complex je berekeningen zijn maar met wat UDF's, Stored Procedures etc. moet je een heel eind komen. Hell, in MSSQL kun je gewoon een .Net DLL laden en je eigen .Net code draaien als je wil.

chicky schreef op donderdag 24 oktober 2019 @ 12:26:
Ik ben meer aan het denken in de richting van "platte" databestanden (een soort CSV bestanden) waar ik vervolgens met een stuk extern software tegen aan ga praten en de boel ga bewerken.
Ik zou dit kunnen maken in bijvoorbeeld python, C++, VB of wat nodig is..

Dan zou ik eerder de data opslaan in de database en daar omheen de software maken; "een soort CSV" kan natuurlijk, maar een beetje structuur, wat indexen en al het andere moois dat een RDBMS met zich meebrengt (referentiële integriteit anyone?) zijn ook heel wat waard...

[ Voor 44% gewijzigd door RobIII op 24-10-2019 12:34 ]

[Freezerator]

En heb je wel eens gekeken of je PowerBI met DAX kan gebruiken? Dat lijkt me een prima oplossing. En het aanleveren van die 25 sheets, zit je daar vast aan excel? Of zou men het ook in een ander formaat willen aanleveren?

[chicky]

Freezerator schreef op donderdag 24 oktober 2019 @ 12:50:
En heb je wel eens gekeken of je PowerBI met DAX kan gebruiken?

Ja, wij maken al gebruik van PowerBI. Maar wij zien PowerBI meer als een analyse tool dan een tool om data mee te bewerken.

En het aanleveren van die 25 sheets, zit je daar vast aan excel? Of zou men het ook in een ander formaat willen aanleveren?

Alles kan, maar Excel heeft zeker voor deze 25 sheets wel het voordeel dat het gemakkelijk werkt, je makkelijk rijen/kolommen kan kopieren/verwijderen en dat het hartstikke goed kan rekenen.
Excel heeft dus echt wel de voorkeur.

[boe2]

Jezus. Ik moet veel met antieke office VBA applicaties prutsen, maar jij zit toch wel op een ander niveau.

Al aan gedacht om als eerste stap die externe excels naar een access applicatie te sturen? Kan je de data al een stuk gemakkelijker herorganiseren en kan je een hoop van je bestaande VBA code overzetten.

[Tsurany]

Je loopt nu al tegen de beperkingen van Excel aan, ik zou daar vooral niet verder mee gaan. Er zijn veel meer applicaties speciaal ontwikkeld voor data analyse op zulke schaal. Een database is wellicht niet geschikt om alle manipulaties en berekeningen uit te voeren maar wel om zulke hoeveelheden data op te slaan.

[chicky]

Says who? Het hangt er een beetje van af hoe complex je berekeningen zijn maar met wat UDF's, Stored Procedures etc. moet je een heel eind komen. Hell, in MSSQL kun je gewoon een .Net DLL laden en je eigen .Net code draaien als je wil.

De berekeningen zijn op zich zelf niet erg complex maar het zijn er wel heel veel op elkaar gestapeld waardoor het wel ingewikkeld wordt om in 1 keer te vangen.

Dan zou ik eerder de data opslaan in de database en daar omheen de software maken; "een soort CSV" kan natuurlijk, maar een beetje structuur, wat indexen en al het andere moois dat een RDBMS met zich meebrengt (referentiële integriteit anyone?) zijn ook heel wat waard...

Ik ben het helemaal eens dat een database grote voordelen heeft in indexen e.d. en ook dat je in een bepaalde structuur wordt gedwongen wat zorgt voor een nettere en beter code.
Eén van de grootste problemen die ik denk te hebben is dat ik itreer door rijen op basis van data in kolommen. Ik denk dat dat heel erg ingewikkeld is om in een database te programmeren.
Ik moet dan eerst de rijen selecteren met een query en vervolgens op de recordset mijn kunstje doen. Alles kan, maar of het handig is

Als ik nu uiteindelijk mijn itraties heb gedaan heb ik een sheet met circa 1.000.000 regels die sorteer ik, voeg hulp kolommen toe, manipuleerd data en verwijder vervolgens allerlei regels op basis van diverse voorwaarden.
Mijn gevoel is dat dit alles gemakkelijker is om dit niet vanuit een database programma te doen maar vanuit een stukje dedicated software.

Wat ik nu in Excel doe, had ik 2 jaar geleden ook niet zo bedacht maar het is wel zo gegroeid. (beetje kind met een waterhoofd).
Ik ben nu alles aan het evalueren en de vraag is wat handig is om met het oog op de toekomst te doen.

[chicky]

Ja, je hebt het precies bij het goede eind en jouw plaatje dekt precies de lading zoals het is gegroeid
Wat ik eigenlijk niet doe, is geen data analyse maar wel heeeel veel data maken op basis van een hele slimme basis die ik itreer en waar ik vervolgens de "dubbele" eruit halen.

De reden dat ik met een slimme maar kleine basis ben begonnen is juist om ervoor te zorgen dat wanneer ik iets aan de Main sheet moet toevoegen, ik dat kan in relatief weinig tijd.

Dat een database een goede plek is om zoveel data op te slaan, daar ben ik het echt wel mee eens.

Een database is wellicht niet geschikt om alle manipulaties en berekeningen uit te voeren

Maar waarmee gaan ik dan mijn data mee manipuleren?

[ Voor 0% gewijzigd door chicky op 24-10-2019 15:29 . Reden: typo ]

[Tsurany]

chicky schreef op donderdag 24 oktober 2019 @ 14:16:
Maar waarmee gaan ik dan mijn data mee manipuleren?

Wat kan je en wat heb je ter beschikking? Probeer vooral in kaart te brengen welke manipulaties uitgevoerd moeten worden, of er een volgordelijkheid in zit en hoe intensief deze manipulaties zijn om in een DB uit te voeren.
En vooral, wat moet er uit komen? Voor wie moet deze data beschikbaar zijn en op welke manier willen ze het gepresenteerd hebben?

Ik kan je wel vertellen een pakket uit deze lijst te pakken maar dan zal het antwoord waarschijnlijk zijn dat je het budget niet hebt om dit aan te schaffen.

[AllesIsVoorbij]

Sla je de bestanden al als xlsb op? Zal je formules niet sneller maken maar helpt wellicht iets tot je iets beters hebt. Zie ook hier: https://analystcave.com/e...el-files-the-xlsb-format/

[chicky]

AllesIsVoorbij schreef op donderdag 24 oktober 2019 @ 14:28:
Sla je de bestanden al als xlsb op? Zal je formules niet sneller maken maar helpt wellicht iets tot je iets beters hebt. Zie ook hier: https://analystcave.com/e...el-files-the-xlsb-format/

Yep, doe ik al.
Een Excel sheet in *.xlsb is bijvoorbeeld 3,5 Mb groot. In *.xlsx is ditzelfde bestand opeens 27 Mb.

[chicky]

Wat kan je en wat heb je ter beschikking?

Excel 2016, Acces 2016
Windows Server 2016 met SQL Server 2017 standaard
PowerBI
Apache server met MySQL
En als ik iets nodig heb, dan kan ik het binnen alle redelijkheid aanschaffen.

Probeer vooral in kaart te brengen welke manipulaties uitgevoerd moeten worden, of er een volgordelijkheid in zit en hoe intensief deze manipulaties zijn om in een DB uit te voeren.

De manipulaties heb ik precies in kaart. Niet moeilijk maar wel veel verschillende voorwaarden en resultaten zijn gestapeld.
Ik kan niet goed inschatten hoe intensief deze manipulaties zijn om in een DB uit te voeren. Eigenlijk vind ik het ook niet zo intressant of het snel gaat of niet. Zolang het maar stabiel is.

En vooral, wat moet er uit komen? Voor wie moet deze data beschikbaar zijn en op welke manier willen ze het gepresenteerd hebben?

Alles wat eruit komt is eigenlijk voor intern gebruik. De format waarin is eigenlijk niet zo belangrijk.
Uiteindelijk importeer ik alles in de SQLserver db.

[JPM85]

Ik zou eens gaan testen met software zoals Knime (is open source & gratis te verkrijgen).

https://www.knime.com

Is een data prep tool, die je niet alleen helpt om alles aan elkaar te koppelen, maar je kan er ook berekening en analysis op los laten.

Heb het zelf enige tijd gebruikt, maar zijn nu over naar Alteryx (betaald).

[NMe]

chicky schreef op donderdag 24 oktober 2019 @ 14:09:
[...]

De berekeningen zijn op zich zelf niet erg complex maar het zijn er wel heel veel op elkaar gestapeld waardoor het wel ingewikkeld wordt om in 1 keer te vangen.

Dan splits je het toch op? Je hebt nu waarschijnlijk stapels VBA, dat zou je in een database mooi in stored procedures kwijt kunnen. Of je zou je data zelf kunnen verrijken door middel van wat triggers die wat van je gegevens alvast redundant opslaan zodat je er meteen mee kan rekenen zonder eerst te aggregeren. Either way is dat alvast honderd keer beter dan een wirwar aan sheets die aan elkaar gekoppeld zijn met verwijzingen tussen velden en sheets en met VBA.

[Ben(V)]

Excel is een bijzonder krachtige tool en als je een goed in thuis bent kun je ontzettend veel mee, maar er zijn beperkingen met name als de bestanden te groot worden.
Excel wil alles in memory doen en als dat niet meer lukt krijg je vaak onverklaarbare problemen.

Neem een access database om de opslag in te doen
Die heeft een prima integratie met excel en dan kun je de berekeningen in excel houden.
Anders gooi je jaren ontwikkeling weg.

[Mugwump]

chicky schreef op donderdag 24 oktober 2019 @ 13:57:
[...]


Ja, wij maken al gebruik van PowerBI. Maar wij zien PowerBI meer als een analyse tool dan een tool om data mee te bewerken.

In de BI wereld doe je de data bewerking vaak in zogenaamde ETL / ELT tooling (extract / transform / load) . Die zorgt ervoor dat je data in een database landt in de juiste vorm, waarna je met bijvoorbeeld PowerBI visualisaties en rapportages maakt.
In de Microsoft wereld kun je daar bijvoorbeeld Azure Databricks en Data Factory voor gebruiken.

[sig69]

Als ik het zo lees zit er ook nog een hoop handwerk in (sorteren, hulpkolommen toevoegen, filteren), kan dat allemaal geautomatiseerd worden?
Is historie nog belangrijk?
Van alle oplossingen die geopperd zijn is alles beter dan Excel eigenlijk op dit moment (behalve Access, helemaal als je een sql server hebt staan).
Kies de oplossing waar je je comfortabel bij voelt, of dat nou pyton, c#, ssis of whatever is. Je hebt je proces goed in beeld, dus bouw het ook gewoon zo. Bron data, in meerdere stappen bewerkingen er op uitvoeren (eventueel tussendoor opslaan als dat handig is), resultaat opslaan.

[YakuzA]

De stap tussen VBA + excel rows/columns en VB.Net + database rows/columns is vaak kleiner dan je denkt.

Hier hebben we wel vaker van dit soort excel applicaties omgezet naar iets dat via IIS + .NET + MSSQL werkt.

[Falcon]

Als het zo hard groeit hou dan ook goed rekening met schaalbaarheid en archivering/opschoning in je nieuwe technische oplossing.

[ Voor 15% gewijzigd door Falcon op 26-10-2019 23:27 ]