Transcript 2013-06-19 ACL vs Excel

ACL vs Excel
Xavier Théorêt
19 juin 2013
Xavier Théorêt…
Cette présentation…
Intro (5-10 min)
Excel vs ACL
• Une bonne question? (ou Pommes vs Oranges?)
Microsoft Excel, dont le nom officiel actuel est
Microsoft Office Excel, est un logiciel de tableur de la
suite bureautique Microsoft Office [...]
[...] Le logiciel Excel intègre des fonctions de calcul
numérique, de représentation graphique, d'analyse de
données (notamment de tableau croisé dynamique) et
de programmation, [...]
Excel vs ACL
• Une bonne question? (ou Pommes vs Oranges?)
ACL data analytics, formerly known as Audit Command
Language, is a data extraction and analysis software
used for fraud detection, prevention and risk
management.
By sampling large data sets, ACL analytics software is
used to find irregularities or patterns in transactions
that could indicate control weaknesses or fraud.
Excel vs ACL
Rép: Oui! Car ce sont tous des CAATs* au sens général du terme
In the most general terms, CAATTs can refer to any computer
program utilized to improve the audit process. Generally, however,
it is used to refer to any data extraction and analysis software.
This would include programs such as spreadsheets (e.g. Excel),
databases (e.g. Access), statistical analysis […] etc.
There are, however, companies that have developed dedicated
specialized data analytic software specifically for auditors.
*Computer-assisted audit techniques (CAATs) or computer-assisted audit tools and techniques (CAATTs)
Excel est différent
• Les cellules sont modifiables…
• …Plus d’un type de données est possible dans
une même colonne.
• …on a même pas besoin de faire des colonnes!
• Excel fait des choix souvent invisibles.
• Problème d’intégrités / sans logs.
ACL est différent
• Les cellules sont non modifiables…
• …Un seul type de données est possible dans une
même colonne.
• …Données en lignes et colonnes
• Les choix fait par ACL doivent être manuellement
validés (rien de caché).
• Son héritage de « ligne de commande » se voit.
• Performant sur les grandes quantités de data.
ACL et Excel
• Permettent de traiter un grand nombre de
données.
• Possède des outils pour traiter l’information,
la manipuler pour lui donner un sens et
répondre à des questions.
ACL et Excel
Y a-t-il un meilleur CAAT? Non abordé ici* car plusieurs
facteurs sont à considérer…!
*Des liens à la fin de cette présentation sont disponibles pour continuer cette
réflexion. Note: Excel et ACL sont partenaires, pour preuve le produit Acerno d’ACL.
La prochaine section tente plutôt d’établir des parallèles
entre l’utilisation d’Excel et d’ACL.
Certaines particularités valent la peine d’être soulignées
pour: 1) diminuer les risques d’erreur et/ou 2) faciliter
l’apprentissage d’ACL.
Partie pratique… les différences
1. Le texte
2. Les nombres
3. Les dates
4. Les sommaires
5. (Les scripts)
Différence ACL #1 – Le texte
• La taille d’un texte est la même pour une colonne
• ACL fait donc une conversion des chaînes de texte
et ajoute des espaces pour combler la différence
de taille.
Ex:
« avion », « auto », « bateau »
devient
« avion□ », « auto□□ », « bateau »
Différence ACL #1 – Le texte
• Résultat:
La fonction
LENGTH d’ACL
devrait toujours
retourner la
même valeur
par défaut
contrairement à
=NBCAR d’Excel.
Différence ACL #1 – Le texte
• Les égalités sont
pareilles avec Excel
car dans ce cas
ACL ne tient pas
Les deux requêtes « CAKE PAN » avec ou sans espace
compte des
retournent le même résultat. Mais ACL tient compte
de la casse (contrairement au défaut d’Excel). Par ex:
blancs…
Table : Metaphor inventory 2002
Différence ACL #1 – Le texte
• Note: La présence de blancs invisibles peut causer des
problèmes sous Excel (ex: RechercheV échoue) pas ACL!
• ACL n’a pas de « =Gauche », « =Left » ou « =Droite »
– Faut utiliser « SUBSTR(ChampTexte, 1, X) »
– l’équivalent de « =STXT » chez Excel.
• De son côté, Excel ne possède pas de fonction
« REVERSE » pour inverser des chaînes.
Différence ACL #2 – Les nombres
• ACL utilise le calcul à
virgule (décimale) fixe.
• 9/2 = 5
• 9.0/2 ou DEC(9,2)/2 = 4.5
•
•
ACL: la précision des données dicte par
défaut la précision du résultat final affiché.
Chez Excel la précision maximale est
conservée en tout temps et elle est
indépendante de celle affichée.
Différence ACL #3 – Les dates
• ACL Analytic 10 introduit plusieurs fonctions de dates.
Mais si vous utilisez la version 9.3 ou
précédente il faut savoir que plusieurs
fonction usuelles d’Excel sont absentes:
•
•
•
•
•
= Annee()
= MOIS()
= Jour()
= JOURSEM()
=No.Semaine()
•
•
•
•
= NOW()
= HEURE()
= MINUTE()
= SECONDE()
La solution consistait généralement à
faire un découpage des dates comme si
elles étaient des chaînes de texte:
Si X est un champ de Date…AAAAMMJJ
• SUBSTR(DATE(X),1,4)  Annee
• SUBSTR(DATE(X),5,2) Mois
• SUBSTR(DATE(X),7,2)  Jour
Différence ACL #4 – Les sommaires
• ACL: Certains champs disparaissent des menu Sommarize /
Crosstab / etc. Pourquoi?
Assistant DUPLICATE
Table : Metaphor inventory 2002
Assistant CROSSTAB
Différence ACL #4 – Les sommaires
• ACL: Certains champs disparaissent des menu Sommarize /
Crosstab / etc. Pourquoi?
Réponse: Sommarize et
Crosstab ne supportent
pas les nombres ni les
dates. Utiliser Zoned() ,
Date() ou String() pour
convertir en texte.
Commentaire: J’ai posé la question
sur le forum d’ACL et on m’a dit
que l’inclusion de nombres dans
CROSSTAB avait été pensé mais
non exécuté faute de temps.
Différence ACL #4 – Les sommaires
• ACL: Certains champs disparaissent des menu Sommarize /
Crosstab / etc. Pourquoi?
Le tableau croisé dynamique d’Excel:
• Plus versatile que son équivalent CROSSTAB d’ACL.
• Les nombres sont acceptés en titre de colonne.
• Plus d’un champ peut être imbriqué en colonne à la fois
• Un simple double-clic permet d’extraire toutes les données d’une catégorie
précise (ex: toutes les transactions Câble pour le mois de janvier 2013).
Différence ACL #5 – script vs formules
• Une formule est:
– ACL: entrée dans la barre de commande ou dans un script. La
syntaxe des mot clé peut être réduite. Ex: SUB ~ SUBSTR
– Excel: entrée dans la barre de formule (F2).
La syntaxe doit être exacte.
(note on n’abordera pas le VB script ici!)
• La formule est identifiée:
– ACL: Par rien
– Excel: Par un signe d’égalité
ou par des accolades
=
{ } (formules matricielles)
Différence ACL #5 – script vs formules
L’opérateur de concaténation:
 ACL : le plus
+
 Excel: la perluette
&
Une variable:
ACL: Le nom d’un champ ou n’importe
quel nom non utilisé placé entre deux
symboles du pourcent % (ex: %Var1%)
Excel: La référence d’une cellule (ex: A2)
Le texte:
 ACL: le guillemet droit simple ou double ‘ ou " Ex: " Voici un guillemet simple ‘ "
 Excel: guillemets double droit
"
Ex: "Voici un guillemet double "" "
Une date:
 ACL: Entre accent grave dans le format `AAAAMMJJ`
 Excel: En nombre ou via DATEVALUE("divers format ")
Merci!
Questions / Commentaires / Trucs?
Documentation ACL vs Excel
Comparaison entre divers CAAT par Richard Lanza dans Fraud Magazine (2006)
http://www.informationactive.com/data/attachments/fraudsoftware.PDF
Excel comme CAAT
• http://www.sethspro.com/CAAT.pptx (massage… ?)
• http://www.uic.edu/classes/actg/actg537/LectureSlidesS08/FE4.Data%20Analytics%20I.ppt
ACL comme CAAT
• Peter Millar (ACL) http://www.dataconsulting.co.uk/Files/ebook_spreadsheets.pdf
• Michael Kano (ISACA SF) http://www.sfisaca.org/images/FC11Presentations/P23.pdf
Historique et comparaison entre ACL et IDEA (vieux - 2004 mais reste pertinent)
http://www.cga-canada.org/en-ca/AboutCGACanada/CGAMagazine/2004/JanFeb/Pages/ca_2004_01-02_dp_doubleclick.aspx