Name: Gestion comptable avec Excel (versions 2016 et 2019)
Rating: 5 (3 reviews)

Descriptif du projet

Notre entreprise exemple vend principalement à l’étranger. Ses clients sont l’Europe et les États-Unis. La facturation est effectuée sur un logiciel de gestion commerciale et les paiements sont directement saisis dans le logiciel comptable.

Autant les comptes clients Europe travaillant avec l’euro sont faciles à suivre, autant les autres comptes étrangers posent de nombreux problèmes de concordance entre les factures et les paiements, en raison de la fluctuation du cours de la monnaie.

Notre objectif va être de créer un tableau de suivi des factures dont la devise est différente de l’euro. Pour cela, nous allons automatiser :

La recherche du cours des devises.
La conversion des montants dus à la date de facturation.
Le calcul du gain ou de la perte de change sur chaque facture.
La comptabilisation des différences de conversion.
L’extraction des factures non réglées.
L’évaluation des gains ou des pertes de change latents (probables) en fin d’exercice comptable.

Importer le cours des devises à partir d’Internet

Où trouver ces informations et sous quelle forme ?

Le but de notre projet est d’avoir le cours des devises quotidiennement et automatiquement. Il n’est pas question de faire une recherche sur Internet tous les jours pour reporter la valeur manuellement dans notre classeur.

Il nous faut un site qui ne se limite pas au cours du jour, mais qui donne accès à l’historique des devises désirées.

Il existe principalement deux types d’historique proposés :

Un fichier à télécharger (format CSV, texte, ou autre…).
Un tableau présent dans la page du site Internet.

Un fichier à télécharger

Le fichier à télécharger nécessite que vous fassiez l’action de retourner sur le site Internet pour récupérer la mise à jour avant de pouvoir vous en servir dans Excel. Ce qui n’est pas si mal si vous souhaitez faire cette mise à jour une fois par mois, ou une fois par quinzaine.

Où se procurer ce type de fichiers ?

Le plus logique est de contacter la banque de l’entreprise. Il serait vraiment étonnant qu’elle ne puisse pas vous fournir ce type de fichiers, soit à partir de son site soit directement.

Il est également possible de se connecter, entre autres, au site de la Banque de France dont le lien est le suivant :

http://www.banque-france.fr/nc/economie-et-statistiques/changes-et-taux/les-taux-de-change-salle-des-marches/parites-quotidiennes.html

Vous pourrez télécharger facilement le fichier qui contient l’historique de toutes les devises.

Nous l’avons fait pour vous, vous le trouverez dans la liste des fichiers que vous avez téléchargés depuis la page Informations générales.

Il se nomme Webstat_Export_20181117.csv.

Voyons comment importer ce fichier à partir d’Excel.

Dans un nouveau classeur, sélectionnez la cellule A1.

Onglet Données - groupe Récupérer et transformer des données - bouton images/04SOB02N.PNG .

Dans la boîte de dialogue Importer des données, recherchez et sélectionnez le fichier Webstat_Export_20181117.csv puis cliquez ensuite sur le bouton Importer : vous obtenez l’écran suivant :

Votre fenêtre peut être sensiblement différente....

Fonctions utilisées pour les calculs

Une fois nos données importées, nous allons nous en servir dans notre tableau de suivi des factures pour convertir les montants des factures. Nous utiliserons les actions et les fonctions suivantes :

Fonction RECHERCHEV : pour renvoyer le cours de la devise correspondant à la date de la facture ou du paiement.
Fonction SI : pour déterminer le lieu de la recherche selon la devise concernée. Nous vous rappelons que nous gérons deux devises, et qu’avec la fonction SI il est possible de proposer jusqu’à 64 réponses au cas où vous auriez à gérer de nombreuses devises. Nous utiliserons également cette fonction pour déterminer si la différence de change est un gain ou une perte, et pour éviter l’affichage des messages d’erreurs lorsque les cellules sont vides, etc.
La gestion des noms : pour faciliter les manipulations dans la formule, nous donnerons des noms aux différentes plages de cellules.

Recopier des formules de calcul

Recopie manuelle : astuce

Si la taille de votre liste était connue à l’avance, il suffirait de saisir votre formule une fois, puis de la recopier sur toutes les lignes de votre tableau à l’avance. Ainsi, vous n’auriez plus à vous soucier de vos formules lors de la saisie des factures.

Mais évidemment, cela est rarement le cas, la taille du tableau final étant souvent méconnue. Vous décidez donc de préparer la structure de votre liste des factures en tapant à l’avance les formules uniquement sur la première ligne, puis de recopier les formules lors de la saisie des factures par l’usage de la poignée de recopie ou par un copier/coller des formules.

Pour gagner du temps, nous vous conseillons de saisir dans un premier temps les factures sans vous soucier des formules, et dans un deuxième temps de double cliquer sur la poignée de recopie de chaque cellule contenant une formule. La recopie se fera aussitôt face à chaque facture saisie pour s’arrêter automatiquement à la première ligne vide.

Option Excel : étendre les formules d’une plage de données

Activer cette option

Le logiciel Excel est habituellement paramétré par défaut pour permettre la recopie automatique des formules. Ce qui signifie que vous saisissez vos factures et automatiquement...

Extraire les factures non réglées en fin d’exercice

En fin d’exercice comptable, il est nécessaire de faire le point sur les factures pour isoler les factures impayées. Il s’agira ensuite d’évaluer le montant de chaque facture par rapport au cours de la devise du dernier jour de l’exercice.

Les filtres avancés

Nous utiliserons les filtres avancés pour extraire les factures. Notre but sera d’extraire uniquement les factures dont la date de paiement n’a pas été renseignée.

Le filtre avancé est un outil qui nécessite la mise en place d’un critère, avant toute utilisation. Ce critère est composé, en règle générale, du nom de la colonne, appelé champ, en dessous duquel sera inscrite la valeur recherchée.

Exemples de critères courants

Ainsi, si votre but est d’extraire la liste des factures d’un même client (client06 par exemple), le critère est à présenter de la manière suivante :

Si vous voulez extraire deux clients, client06 et client83, vous aurez :

Si vous voulez extraire les factures des clients utilisant la devise USD et pour lesquelles des pertes de change ont été calculées :

Pour extraire les factures dont le montant facturé est compris entre 1000 et 2000, vous aurez :

Critères calculés...

Construction pas à pas sans les macros

Nous allons construire ici l’ensemble du projet pas à pas de manière détaillée sans faire appel aux macros.

Les macros seront abordées plus loin dans ce chapitre pour permettre à ceux qui le désirent de s’initier à l’usage des macros.

Importer le cours des devises à partir du Web

Créez un nouveau classeur Excel que vous enregistrerez sous le nom de Factures devises.xlsx.

D’un autre côté, ouvrez votre navigateur et saisissez dans la barre d’adresse l’URL suivante : http://fxtop.com/fr/historates.php

Dans la zone de recherche du site reproduisez les éléments ci-dessous.

Veillez à ce que Moyenne mensuelle ne soit pas cochée, puis cliquez sur Chercher.

Sélectionnez et copiez l’URL qui s’est affichée dans la barre d’adresse de votre navigateur.

Revenez sur Excel : Onglet Données - groupe Récupérer et transformer des données - bouton À partir du web.

Collez l’adresse dans la zone URL de la fenêtre A partir du web, validez par OK.

Sélectionnez Table 2. Cliquez sur le bouton Modifier pour modifier les éléments importés.

Sélectionnez et supprimez la colonne % en cliquant sur le bouton Supprimez les colonnes.

Sélectionnez la colonne EUR/GBP, remplacez le point par la virgule à partir du bouton Remplacer le valeurs.

Modifiez le type des données en sélectionnant Nombre décimal à partir du bouton Type de données du groupe Transformer ou du bouton ABC dans l’en-tête de colonne.

Cliquez sur le bouton Fermer et charger.

Nommez la feuille GBP.

Affichez la fenêtre Propriétés du groupe Requêtes et connexions de l’onglet Données.

Cliquez sur le bouton images/04SOB27N.PNG :

Cochez Actualiser les données lors de l’ouverture du fichier.

Faites les mêmes manipulations sur une nouvelle feuille pour obtenir le cours de la devise USD Dollar américain sans oublier l’actualisation des données à l’ouverture du fichier.

En cas de problèmes pour l’importation des données, nous vous proposons d’ouvrir le fichier Suivi factures...

Construction des macros pas à pas

Continuons le travail dans le fichier Factures devises créé précédemment. Nous allons procéder à son enregistrement dans le type adapté aux macros.

Onglet Fichier - option Enregistrer sous

Dans la boîte de dialogue Enregistrer sous, sélectionnez Classeur Excel (prenant en charge les macros) dans la liste Type.

Cliquez sur le bouton Enregistrer.

Macro de recopie automatique des formules

Rappelons que cette macro nous servira à recopier toutes les formules de la liste des factures lors de chaque nouvelle saisie, soit la recopie des cellules G2, H2, K2, L2 de la feuille Liste Factures.

Commencez d’abord par effacer les formules des cellules G3 à G8, H3 à H8, K3 à K8 et L3 à L8 de la feuille Liste Factures.

Nous travaillerons dans un premier temps sur la recopie de la cellule G2, nous étendrons ensuite le principe aux autres cellules.

Macro enregistrée pour s’aider dans la construction du code

Nous allons créer notre procédure en passant par des étapes de réflexion et de modifications successives de notre code tout en explicitant les raisonnements adoptés dans chaque cas.

Nous allons nous aider de l’enregistreur de macro pour obtenir la syntaxe nécessaire à un copier-coller simple.

Onglet Développeur - groupe Code - bouton Enregistrer une macro

Dans la fenêtre qui s’affiche, saisissez copier_formules dans la zone Nom de la macro, vérifiez que la zone Enregistrer la macro dans contienne Ce classeur, ajoutez dans la zone Description le texte Recopie automatique des formules puis cliquez sur OK.

Faites un clic droit en G2 puis Copier.

Faites un clic droit en G3 puis Coller

Onglet Développeur - groupe Code - bouton Arrêter l’enregistrement

À l’extrême gauche de la barre d’état de la fenêtre de l’application Excel, le bouton images/04SOB64N.PNG