As a hobby project I’ve decided to create my own personal accounts application and not rely upon Quicken (Reckon Accounts in Australia). There is nothing wrong with Quicken as it does (nearly) everything I want it to. I’d like to have more control with finer details and the only way that can happen is if I write my own.
This is for personal accounting and doesn’t need to cater for Orders, Sales, Customers, Inventory etc.
What I do want to record is share trading and purchasing things in foreign currency.
The following is a schema which I’ve designed to date.
It’s based on the double entry accounting method.
- All income, expenses, assets, loans, shares (stocks) are contained in “tbl_Accounts”.
- For every transaction (tbl_Transactions) there will be multiple entries within the posting table (tbl_Posts)
- For each transaction all the posting records will equate to zero (that is, they balance themselves out).
- I’ve included Unit Price and Quantity fields in the posting table as this will cater for share trading and foreign exchange aspects.
- All account balances will be calculated.
- The selling of stocks/shares also includes the table, “tbl_Lot_Sell” as a parcel of shares sold needs to be associated to a prior parcel of stocks purchased.
- It doesn’t yet include ‘end of day’ stock prices.
The schema is still not finished and I’m not going to start coding until I know I’m 90% certain with the schema.
Any suggestions or questions most welcome.
Updated Schema.
Further, now added End of Day stock prices which hooks off the “tbl_Shares” table but otherwise everything is as it was.
Missing: Exchange rates table and period table and/or fields for week or month and year totals
Yes, exchange rate table to be included.
Not sure about a period table for balances. I’m thinking they can be simply calculated as and when they’re needed. If a persuasive argument can be made otherwise I’m likely to change my mind.
I wouldn’t recalculate every transaction each time you need the period totals because with every calculation in foreign currencies and dividing amounts over more than one period you will get problems with rounding errors.
If i may suggest: include in all tables values in local currency and foreign currency and keep cumulative values per account per period in a seperate table. It makes calculations for results of currency-exchanges easier and doesn’t cause rounding errors.
You will also benefit from this if you later decide to offer a form of consolidation of several administrations in your end product.
Purchasing items in foreign currency is not an everyday or every week occurrence, it is infrequent and it’s the same regarding my share trading. If I were to guess the percentage of overall transactions that account for foreign exchange or share trading I’d say it would be 5%, therefore 95% of my transactions are for normal income and expenses and transfers between accounts. Even though foreign exchange and share trading is a minor component I still want to account for it as I do using Quicken.
I haven’t yet accounted for bank reconcilliation but was wondering if this entailed the use of a separate table with a balance recorded as of a certain date. I’m assuming that an additional field would be required in the posting table to ‘tick-off’ a reconciled entry.
The usage of the finished application would be similar to that of Quicken (and Microsoft Money from years ago) where I open an account in a register style which shows all the transactions correctly assigned to debit/credit columns and a continual running balance. It’s the running balance that would be calculated everytime, which obviously includes the ending balance.
If your program is meant to be used as a kind of a budgetplanner for a household or small busines, i can understand that my suggestions are a bit overdone.
I come from a financial background (accountantcy) and i have seen many times that companies decided that a specific customer cost money instead of delivered a profit. When i checked the figures the result was that they made a profit of say 20 % but didn’t exchange the foreign currency immediately and waited too long and the exchange rate was so far lowered that the whole profit and more was lost and the story lead to the wrong conclusion.