Hovedsiden Infosiden Bildegenerering	Vidar Nakling www.nakling.no

Generering av diagrammer og bilder med Excel

Dette er et nedskalert eksempel på et problem som ble løst for et lokalt trykkeri og en større fondsforvalter.

Vi har Excel-lister over fondsinnehavere, hvilke fond de deltar i og når de kjøpte/solgte andeler i fondene, omtrent slik:

Videre har vi ferdige diagrammer over den generelle kursutviklingen gjennom et år i hvert fond. I dette eksempelet opererer vi bare med tre forskjellige fond.

Oppgave: Lag individuelle diagrammer for hver fondsinnehaver og hvert fond, med markering av kjøptidspunkter og salgstidspunkter. I dette eksempelet er hver innehaver begrenset til maks 4 kjøp og 4 salg pr. år, i real-life-utgaven var det langt flere.

Filnavnet for bildene bygges opp av kundenr. og fondsnr. Filen ovenfor er f. eks 1235-3.gif

Bruk av eksempelet

Fil for nedlasting: XLdiagram.exe Dette er en selvutpakkende fil som foreslår å legge filene på C:\VnDemo\XLdiag

Filen Fondseksport.xls kan plasseres hvor som helst. Den er utstyrt med en startknapp og en kombinasjonsboks for valg av filtype for bildene. (Denne er f.ø. laget med Datavalidering, en godt skjult godbit i Excel). Rutinen spør om en datafil, Fonds01.xls er et eksempel på hvordan den kan se ut. Rutinen lager en ny katalog, Fondsfiler, og legger alle bildefilene der. Hvis katalogen eksisterer og det ligger gamle filer der fra før, blir de overskrevet uten spørsmål.

Metoden

For hvert fond, laget vi et sett med halvferdige kombinasjonsdiagrammer med tre dataserier: generell kursutvikling, kjøp og salg, men uten data i de to siste seriene.

Resten av oppgaven ble løst med en rutine som samlet inn info for innehaver og fond og satte datoene inn på riktig sted i datagrunnlaget for diagrammet, eksporterte diagrammet som TIF-fil, slettet datoene og fortsatte på neste innehaver.

Excel VBA og Windows er ikke verdens raskeste plattform for informasjonsbehandling. Likevel, med litt polering av koden kom vi ned i 3 eksporteringer pr. sekund, eller ca 10000 diagrammer i timen.

Om løsningen

Når jeg tok opp denne igjen et års tid etterpå ble jeg litt imponert, strukturen og oversikten var bedre enn jeg husket. Bra jobba, Vidar! Ofte når jeg gjenåpner halvgamle arbeider blir jeg mer oppgitt: Ååh, hvorfor gjorde du det slik, den gangen...

Hovedrutinen er en liten løkke som åpner en datafil, gjør litt forberedelser og så arbeider seg nedover datafilen:

    FormatType = Finnformat()
    Openbook
    MakedataDir
    ThisWorkbook.Activate
    Begynn = Timer
    i = 1 'start på rad 2
    Do Until gBase.Offset(i, 0) = ""
        BestemFond gBase.Offset(i, 3)
        FinnOms gBase.Offset(i, 4), 4
        Statuslinje gBase.Offset(i, 0).Text
        SettInnOms dBase
        BildeNavn = gBase.Offset(i, 0).Text & "-" & gBase.Offset(i, 3).Text
        EierNavn = gBase.Offset(i, 1).Text & ", " & gBase.Offset(i, 2).Text
        EierNavn = "Kunde nr. " & gBase.Offset(i, 0).Text & " - " & EierNavn
        ExportPicture dSheet, BildeNavn, EierNavn, FormatType
        i = i + 1
    Loop

Mesteparten av arbeidet gjøreres av en håndfull subrutiner:

Openbook åpner en datafil, kontrollerer om den har brukbart innhold og setter noen objektvariabler for videre håndtering.

MakedataDir lager en underkatalog for lagring av bildefiler hvis den ikke finnes allerede.

BestemFond finner hvilket fond som opptrer på hver enkelt rad (kun ett fond pr. rad)

FinnOms finner salgs- og kjøpstidspunktene og legger dem i to globale arrayer.

SettInnOms setter tidspunktene inn i diagramdefinisjonen for det aktuelle fondet. Dermed blir diagrammet oppdatert.

ExportPicture eksporterer angitt diagram som bildefil. Mulige formater kan velges på startsiden før hele prosessen begynner.

Hele prosessen styres også av en rekke globale objektvariabler:

gBook er referanse til den åpnede datafilen

gBase er et navigasjonspunkt i datafilen. All leting er i forhold til denne

dBase er utgangspunkt for oppdatering av datagrunnlaget for hvert enkelt diagram. Settes på nytt for hvert fond som skal behandles

dSheet er arket som det aktuelle fondsdiagrammet står på

Utførelsen

Etter litt grubling innimellom andre arbeidsoppgaver ble jeg enig med meg selv om hvordan problemet skulle løses, selve knottingen husker jeg godt, det var en drøy ettermiddag i maisolen, på verandaen med den bærbare. (med TFT-skjerm og en spesiallaget skjermskygge går det helt fint)

Tilbake til toppen av siden

Microsoft and Windows are registered trademarks of Microsoft Corporation.