Les informations générales

version

Il s’agit de la version de la syntaxe du fichier de configuration. Valeur attendue : 1

source

La clef source indique quel est le fichier source : pour chaque ligne présente dans ce fichier, une ligne sera générée en sortie.

file:	le fichier à charger
tab:	optionnellement l’onglet concerné
name:	le nom sous lequel le fichier sera associé.

Le chargement des dépendances

La recherche de valeurs dans d’autres fichiers Excel nécessite de déclarer les fichiers à charger. La liste externals décrit tous les fichiers qui doivent être chargés, et comment les données doivent être associée avec le fichier source.

[externals.target]
  intern_key = ":source.A"
  extern_key = ":A"
  file = "financial.xlsx"
  allow_missing = false

Les clefs suivantes sont nécessaires pour lier les données venant d’un autre fichier :

intern_key

Il s’agit de la colonne servant à faire la liaison dans la source.

file

Le fichier à charger

tab

optionnellement l’onglet concerné

extern_key

Il s’agit de la colonne servant à faire la liaison dans le fichier devant être lié. La clef doit doit etre unique, l’application supprimera les doublons pour ne garder qu’une seule valeur par clef. Il n’est donc pas possible de faire un produit cartésien.

allow_missing

Cette clef optionnelle indique s’il faut autoriser les valeurs manquantes lors dans une liaison. Déclarer allow_missing va également autoriser les doublons sur les valeurs de clef.

Une fois la dépendance vers un fichier externe déclaré, il est possible d’utiliser les colonnes de ce fichier en les référençant directement. Cela revient à faire un RECHERCHEV dans Excel à chaque fois.

Il est possible d’utiliser une référence externe dans une autre source externe, afin de construire des chemins sur plusieurs niveaux :

[externals.acheteur_annuaire]
  intern_key = ":I"
  extern_key = ":A"
  file = "ANNUAIRE.xlsx"

[externals.acheteur_societe]
  intern_key = ":acheteur_annuaire.BJ"
  extern_key = ":A"
  file = "SOCIETES.xlsx"

Les valeurs présentes dans ces colonnes sont pré-traitées pour éviter les erreurs générales lors des imports : les espaces en fin de texte sont supprimés et le texte est passé en capitale.

Définition des colonnes

Une fois les différents fichiers chargés, il est possible de construire le schéma du fichier de sortie en listant toutes les colonnes à générer :

"""function
  ( :A ^ '_' ^ :target.E
  , :E
  )"""

Tri des données

sort = [
  ":mouvements.B",
  ":lib_titres.B",
  "date('%d/%m/%Y', :mouvements.C)",
  ":A",
]

Les données peuvent être triées à l’aide de la section sort. Il est possible de définir plusieurs critères de tri qui seront traités par ordre de priorité. Des fonctions peuvent également être utilisées (dans l’exemple ci-dessus, nous avons un tri sur des dates au format dd/mm/yyyy, si la donnée n’était pas convertie en date, elle serait triée sur le jour avant d’être triée sur l’année).

Filtrer les données

 filters = [
  ":t_operation.B <> ['Cession', 'Restitution', 'Prêt de titres']",
  ":lib_titres.B <> 0",
  ":societe.A <> 0",
]

Un filtre peut être mis en place pour ne conserver que les lignes souhaitées. Les conditions doivent être toutes validées pour que la ligne soit retenue.

Comparaison par groupe

":t_operation.B <> ['Cession', 'Restitution', 'Prêt de titres']"

Les opérateurs de comparaison = et <> peuvent accepter un groupe de valeur plutot qu’une valeur unique. Dans ce cas, la condition est vraie si l’une des valeurs est présente.

Le cas des cellules vides

":B > 0",
"'prefix_' ^ :B",

Les cellules vides peuvent apparaitres dans des colonnes où l’on s’attend à trouver des champs texte, ou des nombres. L’application va traiter ces valeurs vides en fonction du contexte et les considérer comme 0 si elles sont associées avec un numérique, ou "" si elles sont associées à un texte.

Fonctions sur les nombres

abs

Renvoie la valeur absolue d’un nombre.

int

Transforme une valeur en nombre entier.

Fonctions sur le texte

join

"join('-', :A, :target.E, :B)"

Concatène tous les champs en utilisant le premier paramètre comme séparateur. Les valeurs vides sont ignorées, et dans ce cas le séparateur n’est pas répété.

match

Extrait un motif sur la base d’une expression régulière.

"match('hello ([A-Za-z]+)', ':B')"

substring

Extrait une sous-chaine en indiquant la position et la longueur.

"substring(:H, 1, 5)"

trim

Supprime les espaces en début et fin de chaine

"trim(:C)"

upper

Passe le texte en capitale

"upper(:A)"

Fonctions sur les dates

Normalement, les dates sont représentées dans Excel comme un nombre. On peut donc utiliser les opérations standard (addition…) sans avoir à se poser de question sur leur résultat. Toutefois, il est parfois nécessaire de faire des opérations avancées.

date

Converti une date depuis le format texte. Le résultat de la fonction est le nombre de jours écoulés depuis le 31 décembre 1899 (1 correspond au 1er janvier 1900). Cela correspond à la manière dont Excel enregistre une valeur de type date.

"date('%d/%m/%Y', :B)"

year

Extrait l’année (sur 4 chiffres) à partir d’une date. Si la date est donnée directement, il vaut mieux utiliser substring qui est plus simple d’accès. year devient utile s’il y a des calculs associés :

"year( date('%d/%m/%Y', :M) + date('%d/%m/%Y', '31/12/1999') )",

Les fonctions de groupes

Les fonctions suivantes permettent de grouper les données ou de les mettre en correspondance avec d’autres lignes du fichier.

Elles prennent se basent sur deux paramètres supplémentaires :

1. le premier étant les colonnes à identifier pour faire le regroupement,

2. le second étant l’ordre dans lequel les lignes doivent être triées.

   counter([regroupement, …],[tri, …])

   Le tri est ignoré si le regroupement est omi.

counter

Crée un compteur qui s’incrémente tant que les lignes sont identiques.

L’exemple suivant va remettre le compteur à 1 dès que la société ou le titre change, et utilisera la date pour ordonner les valeurs dans ce groupe.

"""counter(
      [:societe.H, :lib_titres.B],
      [date('%d/%m/%Y', :mouvements.C), :A]
  )"""

La fonction peut également être utilisée pour ne conserver que les lignes uniques au sein d’une plage donnée (via un post-traitement où l’on ne garde que les valeurs à 1)

"counter([:C], [:A]) = 1"

Il est possible de construire plusieurs compteurs sur des groupes et des tris différents au sein d’un même fichier.

Si le critère de tri n’est pas donné, l’application va compter le nombre d’éléments pour chaque groupe: toutes les cellules auront le même résultat.

previous

La fonction previous permet d’aller chercher l’élément précédent dans les données.

En fonction du critère de regroupement, la valeur renverra vide (si l’on est sur groupe différent), et la valeur précédente sera recherchée en fonction du critère de tri donné.

previous(expression, [regroupement, …], [tri, …])

"""previous(
  :ths.G,
  [:socs.EB, :socs.L, :ths.E],
  [:ths.H])"""

sum

La fonction sum permet de calculer la somme d’une colonne donnée sur un critère de regroupement.

Si le critère de tri n’est pas donné, l’application va calculer la somme totale en fonction du critère de regroupement : toutes les cellules auront le même résultat.

sum(expression, [regroupement, …], [])

"""sum(
  :shares.K,
  [:shares.A, :shares:D],
  [])"""

Au contraire, si un tri est donné, l’application va accumuler la somme tout au long des valeurs rencontrées, et selon l’ordre du tri.

min

La fonction min permet d’aller chercher le premier élément du groupe (c’est à dire le plus petit selon l’ordre de tri)

min(expression, [regroupement, …], [tri, …])

"""min(
  :mouvements.C,
  [:societe.A, :I],
  [:mouvements.C])"""

exemple d’utilisation : rechercher la première date d’achat.

max

La fonction max permet d’aller chercher le dernier élément du groupe.

max(expression, [regroupement, …], [tri, …])