Accounting Spreadsheet

• ODOO.ACCOUNT.BALANCE() formula to get account balance over time

• P&L (Profit & Loss) analysis with detailed drill-down to journal entries

• Interactive balance sheet with asset and liability analysis

• ODOO.ACCOUNT.MOVE.LINE() formula to query accounting journal entries

• Financial pivot tables with group by account, partner, analytic

• Integration with chart of accounts and account hierarchy

• Automatically updates when posting new journal entries

• Export to Excel with formulas and formatting preserved

1. Go to Accounting > Reporting > Spreadsheet

2. Create new spreadsheet or open "Account Analysis" template

3. Enter formula: =ODOO.ACCOUNT.BALANCE("account_code", date_from, date_to)

4. Example: =ODOO.ACCOUNT.BALANCE("400000", "2024-01-01", "2024-12-31")

5. Use cell references for dates to easily change analysis period

6. Create comparison with previous period: =ODOO.ACCOUNT.BALANCE("400000", A1, A2)

7. Add calculated columns: Variance, % Change, YTD totals

8. Use conditional formatting to highlight positive/negative changes

• Create columns for Current Period, Previous Period, Budget, Variance

• Add % of Revenue calculations for each line item

• Use named ranges for account codes for easy maintenance

• Add drill-down links to detailed account reports

1. Basic formula: =ODOO.ACCOUNT.MOVE.LINE("account_code", filters)

2. Filter by partner: =ODOO.ACCOUNT.MOVE.LINE("100000", {"partner_id": 5})

3. Filter by date range: {"date_from": "2024-01-01", "date_to": "2024-12-31"}

4. Filter by journal: {"journal_id": 1} or {"journal_type": "sale"}

5. Filter by analytic account: {"analytic_account_id": 10}

6. Combine multiple filters: {"partner_id": 5, "date_from": "2024-01-01"}

7. Use SUMIF, COUNTIF with move line data

8. Create pivot table from move line data with group by dimensions

• Assets: Current Assets (Cash, Receivables, Inventory) + Fixed Assets

• Formula: =ODOO.ACCOUNT.BALANCE("100000:199999", date)

• Liabilities: Current Liabilities + Long-term Liabilities

• Formula: =ODOO.ACCOUNT.BALANCE("200000:299999", date)

• Equity: Share Capital + Retained Earnings + Current Year Profit

• Formula: =ODOO.ACCOUNT.BALANCE("300000:399999", date)

• Validation: Total Assets = Total Liabilities + Equity

• Add comparative columns for multiple dates

• Calculate financial ratios: Current Ratio, Quick Ratio, Debt-to-Equity

• Trend analysis with line charts for key balance sheet items

• Use Insert > Pivot Table from move line data

• Drag and drop fields to configure pivot structure

• Add calculated fields for ratios and percentages

• Apply filters to focus on specific accounts or periods

• Create pivot charts to visualize trends

• Refresh pivot when accounting data updates

• Import chart of accounts into separate sheet as reference

• Use VLOOKUP to get account name from account code

• Create account groups with SUMIF by account code ranges

• Maintain account hierarchy with parent-child relationships

• Filter accounts by account type: Asset, Liability, Equity, Income, Expense

• Use data validation for account code inputs

• Create dropdown lists from active accounts

• Link to account detail view in Odoo

1. Create summary sheet with high-level financial metrics

2. Add hyperlinks from summary numbers to detail sheets

3. Use HYPERLINK() formula: =HYPERLINK("#DetailSheet!A1", "View Details")

4. Create detail sheets with ODOO.ACCOUNT.MOVE.LINE() data

5. Add filters and slicers so users can filter data

6. Use conditional formatting to highlight exceptions

7. Create navigation buttons between sheets

8. Export entire workbook with all drill-down functionality

• Use named ranges for account codes and date parameters

• Create separate sheets: Data, Calculations, Presentation

• Document formulas with comments for easy maintenance

• Set up data validation to prevent input errors

• Use conditional formatting for negative values and exceptions

• Create template spreadsheets for recurring reports

• Set up auto-refresh schedule to keep data current

• Protect formula cells to prevent accidental changes

• Version control for important financial spreadsheets

• Export to PDF with print settings configured

• Share with appropriate access rights (view-only vs edit)

• Backup spreadsheets before major changes