{"id":1107,"date":"2025-11-26T13:33:52","date_gmt":"2025-11-26T12:33:52","guid":{"rendered":"https:\/\/augusta.icone.ovh\/?page_id=1107"},"modified":"2025-11-26T17:03:29","modified_gmt":"2025-11-26T16:03:29","slug":"methodologie-trace-de-la-balance-des-comptes","status":"publish","type":"page","link":"https:\/\/augusta.icone1.ovh\/index.php\/methodologie-trace-de-la-balance-des-comptes\/","title":{"rendered":"M\u00e9thodologie trac\u00e9 de la balance des comptes"},"content":{"rendered":"\n

Cette page retrace les diff\u00e9rentes \u00e9tapes effectu\u00e9es pour r\u00e9cup\u00e9rer les informations des comptes de la copropri\u00e9t\u00e9 et de tracer la courbe de la balance du compte de la r\u00e9sidence Augusta.<\/p>\n\n\n\n

Les op\u00e9rations suivantes sont effectu\u00e9es:<\/p>\n\n\n\n

    \n
  1. T\u00e9l\u00e9chargement des relev\u00e9s de compte bancaire de la r\u00e9sidence sur le site “G\u00e9rer ma copro”. Chacun des documents t\u00e9l\u00e9charg\u00e9s est au format pdf: op\u00e9ration manuelle<\/li>\n\n\n\n
  2. Pour chaque relev\u00e9 extraction des tableaux au format xlsx avec chatgpt et insertion dans une feuille du fichier releves_bancaires.xlsx<\/em><\/strong><\/li>\n\n\n\n
  3. g\u00e9n\u00e9ration d’un fichier excel image de releves_bancaires-balancees.xlsx<\/em><\/strong> avec ajout de la balance courante sur chaque feuille ainsi que un fichier concat\u00e9nant l’ensemble des op\u00e9rations sur une seule feuille: operations_concatenees.xlsx<\/em><\/strong>. On utilise le script python script.py<\/em><\/strong> donn\u00e9 par chatgpt<\/li>\n\n\n\n
  4. G\u00e9n\u00e9ration de la courbe de la balance du compte en fonction du temps avec le script python genegraphe.py<\/em><\/strong> donn\u00e9 par chatgpt: balance.jpg<\/em><\/strong><\/li>\n<\/ol>\n\n\n\n

    Script de g\u00e9n\u00e9ration de la balance<\/h2>\n\n\n\n
    import pandas as pd\nimport numpy as np\nimport os\nfrom openpyxl import load_workbook\n\ndef clean_to_number(x):\n    try:\n        return float(str(x).replace('\\u202f','').replace('\\xa0','').replace(' ', '').replace(',', '.'))\n    except Exception:\n        return np.nan\n\nrepertoire_travail = os.path.dirname(os.path.abspath(__file__))\nfichier_entree = os.path.join(repertoire_travail, \"releves_bancaires.xlsx\")\nfichier_sortie_concatenation = os.path.join(repertoire_travail, \"operations_concatenees.xlsx\")\nfichier_sortie_balances = os.path.join(repertoire_travail, \"releves_bancaires-balancees.xlsx\")\n\nwb = load_workbook(filename=fichier_entree, data_only=True)\nsheet_names = [ws.title for ws in wb.worksheets]\n\nall_data = []\n\nfor sheet in sheet_names:\n    df = pd.read_excel(fichier_entree, sheet_name=sheet, dtype=str)\n    df.columns = [str(c).strip() for c in df.columns]\n    df = df.reset_index(drop=True)\n    \n    # Nettoyage des valeurs num\u00e9riques\n    df['D\u00e9bit'] = pd.to_numeric(\n        df.get('D\u00e9bit', pd.Series(['0'] * len(df)))\n          .astype(str)\n          .str.replace('\\u202f', '').str.replace('\\xa0', '')\n          .str.replace(' ', '').str.replace(',', '.'),\n        errors='coerce').fillna(0)\n    df['Cr\u00e9dit'] = pd.to_numeric(\n        df.get('Cr\u00e9dit', pd.Series(['0'] * len(df)))\n          .astype(str)\n          .str.replace('\\u202f', '').str.replace('\\xa0', '')\n          .str.replace(' ', '').str.replace(',', '.'),\n        errors='coerce').fillna(0)\n    \n    # Utilise colonne F (\"b\") pour le calcul\n    b_col = 'b' if 'b' in df.columns else df.columns[5]\n    b_vals = df[b_col].apply(clean_to_number)\n\n    balances = []\n    for idx, row in df.iterrows():\n        b_val = b_vals.iloc[idx]\n        if not np.isnan(b_val):\n            balance = b_val\n        elif idx == 0:\n            balance = np.nan\n        else:\n            prev_balance = balances[-1]\n            if np.isnan(prev_balance):\n                balance = np.nan\n            else:\n                balance = prev_balance + row['Cr\u00e9dit'] - row['D\u00e9bit']\n        balances.append(balance)\n\n    df['balance'] = balances\n    df['Mois'] = sheet\n    all_data.append(df.copy())\n\n# Concat\u00e9n\u00e9 : on garde uniquement les transactions avec une \"date d'op\u00e9ration\" pr\u00e9sente\nall_months = pd.concat(all_data[::-1], ignore_index=True)\n# Identifie la colonne date d'op\u00e9ration (prend la premi\u00e8re correspondance)\ndate_col = None\nfor candidate in [\"Date op\u00e9.\", \"Dateop\u00e9.\", \"Date op\u00e9\", \"Date_op\u00e9\", \"Date\\nop\u00e9\/\", \"Dateop\u00e9\"]:\n    if candidate in all_months.columns:\n        date_col = candidate\n        break\n\nif not date_col:\n    print(\"Colonnes disponibles :\", all_months.columns.tolist())\n    raise ValueError(\"Aucune colonne date d'op\u00e9ration trouv\u00e9e !\")\n\nfiltered = all_months[all_months[date_col].astype(str).str.strip().str.len() >= 6]\nfiltered.to_excel(fichier_sortie_concatenation, index=False)\n\nwith pd.ExcelWriter(fichier_sortie_balances, engine='openpyxl') as writer:\n    for df, sheet_name in zip(all_data, sheet_names):\n        df.drop(columns=[\"Mois\"], errors='ignore').to_excel(writer, index=False, sheet_name=sheet_name)\n\nprint(\"Traitement termin\u00e9\u202f: balance calcul\u00e9e, concat\u00e9n\u00e9 filtr\u00e9 sur les lignes avec date d'op\u00e9ration.\")\n<\/code><\/pre>\n\n\n\n
    Script de g\u00e9n\u00e9ration de la courbe de la balance<\/h2>\n\n\n\n
    import pandas as pd\nimport matplotlib.pyplot as plt\nimport os\n\n# Param\u00e8tres fichiers\nrepertoire_travail = os.path.dirname(os.path.abspath(__file__))\nfichier_entree = os.path.join(repertoire_travail, \"operations_concatenees.xlsx\")\nfichier_sortie = os.path.join(repertoire_travail, \"balance.jpg\")\n\n# Lecture des donn\u00e9es\ndf = pd.read_excel(fichier_entree, dtype=str)\ndf['balance'] = pd.to_numeric(df['balance'], errors='coerce')\ndf['Dateop\u00e9.'] = pd.to_datetime(df['Dateop\u00e9.'], errors='coerce')\n\n# Filtrage des lignes valides uniquement\ndf = df.dropna(subset=['Dateop\u00e9.', 'balance'])\ndf = df.sort_values(by='Dateop\u00e9.')\n\n# Trac\u00e9 du graphique\nplt.figure(figsize=(12,6))\nplt.plot(df['Dateop\u00e9.'], df['balance'], marker='o', linestyle='-', color='navy')\nplt.xlabel(\"Date de l'op\u00e9ration\")\nplt.ylabel(\"Balance (\u20ac)\")\nplt.title(\"\u00c9volution du solde du compte bancaire\")\nplt.grid(True)\nplt.tight_layout()\nplt.savefig(fichier_sortie, format=\"jpg\", dpi=200)\nplt.close()\n\nprint(f\"Graphique enregistr\u00e9 : {fichier_sortie}\")\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
    Cette page retrace les diff\u00e9rentes \u00e9tapes effectu\u00e9es pour r\u00e9cup\u00e9rer les informations des comptes de la copropri\u00e9t\u00e9 et de tracer la courbe de la balance du compte de la r\u00e9sidence Augusta. Les op\u00e9rations suivantes sont effectu\u00e9es: Script de g\u00e9n\u00e9ration de la balance Script de g\u00e9n\u00e9ration de la courbe de la balance<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","footnotes":""},"class_list":["post-1107","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/pages\/1107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/comments?post=1107"}],"version-history":[{"count":3,"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/pages\/1107\/revisions"}],"predecessor-version":[{"id":1123,"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/pages\/1107\/revisions\/1123"}],"wp:attachment":[{"href":"https:\/\/augusta.icone1.ovh\/index.php\/wp-json\/wp\/v2\/media?parent=1107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}