Sample Database Design for Financial Accounting

Could someone please lead me to a sample database for a financial accounting system?

Double-entry accounting libraries for Java?

What double-entry accounting libraries are available for Java?

Accounting Database - storing a transaction

You make a gaming website where the user can buy gaming credits and the funds are deposited/credited into the user's virtual account to play some game etc...etc..

1

If you got an accountant to record the transaction, it would be recorded like this (maybe a bit more complex but you get the point)

TRANSACTION
PK_ID1 Cash      - $10 (System)
PK_ID2 Deposit        $10 (System)

TRANSACTION
PK_ID3 Bank Account      - $10 (John)
PK_ID4 Deposit        $10 (John)

2

As a developer, do you really need to waste 2 extra records? why not just record it like this…(then you might store information where the funds came from, status in other columns under the same deposit record)

TRANSACTION
PK_ID1 Cash      - $10 (system)
PK_ID2 Deposit        $10 (John)

Is there any real advantage of option #1 over option #2 and vice visa?

EDIT: modified question, removed CR, DR and replaced with a sign.

xbrl us gaap contextRef standard?

Please tell me this is standardized, or at least there is some way to decipher it.

I'm looking at these 3 random 10-Qs and Ks from 2012Q4.

1. ftp://ftp.sec.gov/edgar/data/1001288/0001001288-12-000041.txt
2. ftp://ftp.sec.gov/edgar/data/1002037/0001193125-12-506687.txt
3. ftp://ftp.sec.gov/edgar/data/1002360/0001000096-12-000199.txt

The line items are standardized; however, there are many of them per document. For instance, one document will have a few us-gaap:OperatingIncomeLosses, and the only way I can tell that they denote which period they are reporting is with contextRef. That's where things get complicated.

I see no discernible pattern. Sometimes they're ascending then descending. They all have their own date formats. Some do multi-period grouping. Some don't.

I've tried to find a standard on this. No go.

Does anyone have any ideas?

Many thanks!

Double-Entry Accrual Accounting Concept [closed]

I have an accounting question regarding property management.

Step 1: Residential Owner: Peter

I charge Peter $200 for month management fee. So:

Type      Name     Debit Account        Credit Account      Amount
------    -----    ------------------   --------------      ------    
Charge    Peter    Management Income    Acc Receivable      200.00      

Step 2:

I charge Peter $50 for Gas Utilities. So:

Type      Name     Debit Account        Credit Account      Amount
------    -----    ------------------   --------------      ------   
Charge    Peter    Utilities Income     Acc Receivable      50.00      

Here my balance for Acc Receivable = 250 and Management Income = 200 And Utilities Income = 50.

Now, Peter is going to pay me $200 for the management fee... so

Step 3:

Type      Name     Debit Account        Credit Account      Amount 
------    -----    ------------------   --------------      ------ 
Payment   Peter    Acc Receivable       Management Income   200    

Here my balance for Acc Receivable = 50, Management Income = 0 and Utilities Income = 50

I have seen that the payment automaticaly generates another record in the Undeposited Funds because is money I received but that has not been deposited in the bank yet.

My question is what is the Debit Account and Credit Account for that transaction or how can I handle it.

Type      Name     Debit Account        Credit Account      Debit     
------    -----    ------------------   --------------      ------    
Payment   Peter    Undeposited Funds        **???**         200.00      

Because when I deposit that amount I know that the next record should look like this:

Type      Name     Debit Account        Credit Account      Debit     
------    -----    ------------------   --------------      ------    
Deposit   ME       Bank Account         Undeposited Funds   200.00   

Any clue on how to deal with this?? When the Undeposited Funds record is created what is the credit account??

Thanks

Evenly divide a dollar amount (decimal) by an integer

I need to write an accounting routine for a program I am building that will give me an even division of a decimal by an integer. So that for example:

$143.13 / 5 =

28.62
28.62
28.63
28.63
28.63

I have seen the article here: http://stackoverflow.com/questions/577427/evenly-divide-in-c, but it seems like it only works for integer divisions. Any idea of an elegant solution to this problem?

Calculating Cost of Goods Sold

I have two tables, one for incoming inventory and one for outgoing inventory that look more or less like this:

               purchase (incoming)
--------------+----------+-------+-----------
 inventory_id | quantity | price | timestamp 
--------------+----------+-------+-----------
 bobble       |        1 | $1.00 | 2014-01-01
 trinket      |        2 | $1.00 | 2014-01-02
 trinket      |        2 | $2.00 | 2014-01-03
--------------+----------+-------+-----------


               sale (outgoing)
--------------+----------+-------+-----------
 inventory_id | quantity | price | timestamp
--------------+----------+-------+-----------
 trinket      |        1 | $3.00 | 2014-01-04
 bobble       |        1 | $3.00 | 2014-01-05
 trinket      |        2 | $3.00 | 2014-01-06
 trinket      |        1 | $3.00 | 2014-01-07
--------------+----------+-------+-----------

I would love to have a view that looks looks like this:

                             sale_with_cost_of_goods
--------------+----------+-------------+------------+-----------+-----------+----------
 inventory_id | quantity | total_price | timestamp  | cogs_fifo | cogs_lifo | cogs_avg 
--------------+----------+-------------+------------+-----------+-----------+----------
 trinket      |        1 |       $3.00 | 2014-01-04 |     $1.00 |     $2.00 |    $1.50 
 bobble       |        1 |       $3.00 | 2014-01-05 |     $1.00 |     $1.00 |    $1.00 
 trinket      |        2 |       $6.00 | 2014-01-06 |     $3.00 |     $3.00 |    $3.00
 trinket      |        1 |       $3.00 | 2014-01-07 |     $2.00 |     $1.00 |    $1.50
--------------+----------+-------------+------------+-----------+-----------+----------

I can handle the total price and average cost of goods sold, but the FIFO and LIFO queries are kicking me where it hurts.

Is this reasonable or even possible? Any help would be greatly appreciated.

Multi-Country Accounting System Default currency

We are developing an enterprise-size accounting system and we have logical design problem which we couldn't solve so far.

according to the GAAP standards all the financial transactions have to be stored in one currency only in the database which is pretty obvious and logical concept. but how you can satisfy this concept when you have Multi-Country branches?

our scenario: 1. we have branch in US where the market deal with US dollar as default currency.
2. we have many branches in Europe where the market deal with Euro as default currency.
3. we have the HQ in Dubai where the market deal with AED as default currency.
4. in order to have minimum down-time each branch have a local deployment of the system.
5. all local systems must periodically sync the data with central DB located in the HQ.

Questions:
1. what is the solution used by ERPs "like SAP, SAGE and MS-dynamics" to solve this problems?
2. we thought of creating two default currencies:
a. Global default currency which will be AED in our scenario.
b. Local default currency "AED in HQ, US dollar in US and Euro in Europe".
Is it acceptable solution or Is it considered a violation for the accounting standards??
3. please provide references (if any)?

Thanks,

What's the best way to ensure balanced transactions in a double-entry accounting app?

What's the best way to ensure that transactions are always balanced in double-entry accounting?

I'm creating a double-entry accounting app in Django. I have these models:

class Account(models.Model):
    TYPE_CHOICES = (
        ('asset', 'Asset'),
        ('liability', 'Liability'),
        ('equity', 'Equity'),
        ('revenue', 'Revenue'),
        ('expense', 'Expense'),
    )

    num = models.IntegerField()
    type = models.CharField(max_length=20, choices=TYPE_CHOICES, blank=False)
    description = models.CharField(max_length=1000)


class Transaction(models.Model):
    date = models.DateField()
    description = models.CharField(max_length=1000)
    notes = models.CharField(max_length=1000, blank=True)


class Entry(models.Model):
    TYPE_CHOICES = (
        ('debit', 'Debit'),
        ('credit', 'Credit'),
    )

    transaction = models.ForeignKey(Transaction, related_name='entries')
    type = models.CharField(max_length=10, choices=TYPE_CHOICES, blank=False)
    account = models.ForeignKey(Account, related_name='entries')
    amount = models.DecimalField(max_digits=11, decimal_places=2)

I'd like to enforce balanced transactions at the model level but there doesn't seem to be hooks in the right place. For example, Transaction.clean won't work because transactions get saved first, then entries are added due to the Entry.transaction ForeignKey.

I'd like balance checking to work within admin also. Currently, I use an EntryInlineFormSet with a clean method that checks balance in admin but this doesn't help when adding transactions from a script. I'm open to changing my models to make this easier.

Financial Account pattern implementation in Clojure: ref or agent?

I'm working my way through Fowler's Analysis Patterns and programming examples for myself in Clojure as way of developing a better understanding of both.

Putting persistence/durability issues to the side for the moment[1], it seems that Clojure refs with their synchronization would be the obviously best approach.

On the other hand, given Posting Rules that are triggered by entries into the account, producing more transactions in yet more accounts, perhaps agents, and their asynchronous updates would be better. At this point it looks like I might have to try a bit of both. Anybody have any suggestions on this particular design decision?

[1] I'm assuming if I can get the functionality and concepts working nicely, I'll be able to map everything into a sensible DB schema later.

Financial tracking for software projects

The team I'm part of manage a number of software projects - and most of the stuff we do is end to end, from requirements tracking, to project management to purchasing and setup - a big pain is tracking of financials as we have a whole process to go through for our financials. At the moment we use a spreadsheet and store all the invoices and purchase orders in a shared folder. Its difficult to capture expenses and relate them back to projects that are ongoing or completed. Anyone got better ideas? Hope this topic is relevant.

Programmatically pushing data to Quickbooks Online?

Our company uses Quickbooks Online to track our books. When our application bills a customer, it would be nice to have that information recorded automatically in QB rather than logging in to QB Online to fill in the info.

Is there a way to accomplish this using Quickbook's APIs? The Intuit developer information is very confusing; it is mainly oriented towards extending the desktop version of Quickbooks or developing apps to publish in Intuit's app store.

Have you successfully created a web or other service-side app that was able to pull data to or from QB Online?

Best practices for doing accounting in Python

I am writing a web2py application that requires summing dollar amounts without losing precision. I realize I need to use Decimals for this, but I've found myself having to wrap every single number I get from the database with:

Decimal(str(myval))

Before I go crazy adding that to all of my code, is there a better way? I'm new to Python, so it's very possible that I am overlooking something obvious.

Edit: My database is MS SQL Server and I'm storing the amounts in SQL Server money fields (I believe the implementation is analogous to Decimal, ie integer math, not floating point).

I am connecting to the db through the web2py framework (which uses pyodbc for SQL Server connections). I believe web2py has some support for decimal types. For example, my web2py field definitions look like: Field('Amount','decimal(19,4)') However, when I return a value from the database using web2py's .executesql method it returns the value as a float and not a Decimal.

Edit: This appears to be an issue with FreeTDS and MS SQL Server. As Massimo stated in the comments, web2py supports this properly and returns a Decimal (if it can). It turns out this is only an issue in my production environment (Linux). I am using the FreeTDS driver to connect to MS SQL and it appears to be translating the MS SQL money type to a python float.

I think Alex Martelli's answer is pointing in the right direction. Does anyone have any experience with FreeTDS, MS SQL, and python? I think this probably warrants its own question, so I'll move this discussion... (new question posted here: FreeTDS translating MS SQL money type to python float, not Decimal)

Update: There was in fact a bug in FreeTDS. This was fixed in the CVS head of FreeTDS as of August 4, 2010.

Accounting System for Winforms / SQL Server applications [closed]

If you were going to write a vertical market C# / WinForms / SQL Server application and needed an accounting "engine" for it, what software package would you chose ?

By vertical market, I mean the application is intended to solve a particular set of business problems, not be a generic accounting application. Thus the value add of the program is the 70% of non-accounting related functionality present in the finished product. The 30% of accounting functionality is merely to enable the basic accounting needs of the business.

I said all that to lead up to this: The accounting engine needs to be a royalty-free runtime license and not super expensive. I've found a couple C#/SQL Server accounting apps that can be had with source code and a royalty free run time for $150k+ and that would be fine for greenfield development funded by a large bankroll, but for smaller apps, that sort of capital outlay isn't feasible. Something along the lines of $5k to $15k for a royalty-free runtime would be more reasonable. Open-source would be even better.

By accounting engine, I mean something that takes care of at a minimum:

• General
• Ledger
• Invoices
• Statements
• Accounts Receivable
• Payments / Credits

Basically, an accounting engine should be something that lets the developer concentrate on the value added (industry specific business best practices / processes) part of the solution and not have to worry about how to implement the low level details of a double entry accounting system.

Ideally, the accounting engine would be something that is licensed on a royalty free run-time basis.

Suggestions, please ?

MySQL Double Entry Accounting System Database Design?

i am going to create a database for double-entry accounting system in MySQL.

i recently read the article: http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html

i found in this article that it would be convenient to have three tables ACCOUNT, JOURNAL and POSTING:

ACCOUNT(accountID, accountName)
JOURNAL(journalID, journalType)
POSTING(postingID, journalID, accountID, amount)

the article described that if an account is debited, value of the 'amount' field will be positive, else it will be negative.

now for the POSTING table above, i have other two options as well..

(1) POSTING(postingID, journalID, accountID, isDr, amount)

in this option, 'isDr' field is a boolean. if the account is debited, isDr field will contain value "true", otherwise "false". and the 'amount' field will always have a positive value.

(2) POSTING(postingID, journalID, accountID, debitAmount, creditAmount)

here, if the account is debited, i will store amount in 'debitAmount' field, else i will store it in 'creditAmount' field.

so, exactly which option from above three is better to use??