There are only two categories of people: those who keep track of their income and expenses and those who don't. There are plenty of reasons to track or not to track, but generally, in average, people managing their finances have them in better shape. With all due respect, we shall not promote this software to the do-not-track-people, but will rather concentrate on the folks who have already attempted to use their PCs to figure out their financial status.
There are two major subcategories, though. The first, primary category, using MS Excel or its clones as a convenient calculator, putting some data here and there into the work sheet and making "research" by summing up ranges of various kinds of numbers. This is actually, a good approach because of the transparency factor. If you are interested in a particular issue, MS Excel gives you a wonderful opportunity to explore it in all dimensions. There are millions of people worldwide doing daily researches of all sorts in Excel, and they cannot be wrong in selecting this tool.
Excel approach has got one serious disadvantage - it is extremely time consuming. One has to be a salaried 9 to 5 employee to have enough time for such researches. Moreover, frankly speaking, keeping track of your expenses is not an exciting task. Seeing your money flow somewhere is rather frustrating, so after doing one or two excel researches, people unconsciously try to avoid this painful undertaking in future. Another approach would be to install a comprehensive, full scale accounting package, like Quicken or MS Money. It does not take too much time to learn how to use it, or to make the actual data entries. You can also take advantage of numerous pre-built reports. It is a good approach to keeping track of your cash flow too, as long as you are satisfied with the reporting functionality. The main, unavoidable disadvantage of the pre-built software is its inflexibility and non-transparency. Sooner or later you are likely to run into a transaction that cannot be adequately recorded without adding a fictional account or a dummy counterpart. Non-transparency means that after getting an exciting or depressing report from the program, you cannot actually audit it directly. The algorithms, how the program arrived to the particular result is well hidden within the software code. Tyoma's Family Finance targets the small segment of people who want to have complete control over their accounting software and, at the same time, not to waste their time on Excel researches. Tyoma's Family Finance is a small MS Access application by programming standards. It consist of only 2 (two!) data entry forms, 5 (five) tables and about one dozen of forms and queries for reporting results. At the same time, it has everything our family needs, and if you need something extra, you can easily adjust a form or a module.
Unzip the familyFinance.zip file somewhere on you file system. We recommend using Windows XP, current version has not been tested under Windows 2000. Double click on the file icon, your computer should start MS Access and show the main menu - not beautiful, but practical. Click on the "Data entry" button to open the Data Entry form.
The first field "From" consists of the list of all family monetary accounts - banking accounts, credit cards, trading accounts, mutual funds and cash. It is important to set up all your accounts correctly; otherwise, you will mislead yourself in terms of your financial situation. The addition of a new account is described in the section Banks. Select account for the transaction, if it is by chance not a default account. To set up the default account, right click on the drop-down field, select Properties from the list and adjust value for "Default Value" field.
Date is the date and time of the transaction. By default, current date and time are used, but it can be adjusted to the time in the past. It is not allowed to record transactions in the future, because this transaction will probably not happen. It is also not allowed to record a transaction in the closed period, i.e. in the period that is considered to have been audited and approved. In order to adjust data entry in the closed period, the period has to be reopened, see section "Unlock". The look and feel, including language for the date is defined by Windows. To adjust the date format, open Control Panel, click on Regional and Language Options, on the Regional Options Tab either select the default region of your preference, or for the fine-tuning select "Customize" and adjust Short date format and Time format entries.
Tax is the percentage of the current amount that should be virtually reallocated into the general investment fund. Concept of the virtual allocating to the investment found allows you an additional freedom in monitoring your long term financial status. Your investments can be spread over the number of various instruments, or sometimes even reside on a regular saving account or still be temporarily in cash. Depending on the initial assumptions of your saving strategy, you may wish to allocate all dividends and interest you received on your investment instruments back into investments, so that the overall investment value keeps pace with the inflation. In this case you select 100 (100%) from the drop down menu to be added to your virtual investment account. Unsuccessful performance of your investment account can result in investment loss, which should be recorded as a loss related to the investment only (100%).
Depending on the home finance basic principles, some categories of the regular income can be allocated to the investment (savings for the future). A typical saving ratio for a German household is approximately 10% on all earnings, which corresponds to selection of 10 from the drop down menu. You may also have a more complicated or a simpler structure for separation of day-to-day transactions from the investment activities. To adjust possible percentage rates, right click on the tax drop-down menu, select Properties from the list and adjust Row Source property, it should be a list of numbers, separated by semicolon (as a rule, it generally depends upon Regional Options settings). There is more information about tax and investment polices in the section "Summary".
Clearly, most of the day-to-day transactions are not related to investments and savings, therefore, the default value of the Tax field is set to zero.
Line is an expense category. Select the Line either with a mouse, or, when your categories do not start with the same letter, simply by pressing the first letter key on the keyboard. After selecting the Line it is recommended to immediately select from the How drop-down list.
"How" is an expense sub-category. Although this is not a mandatory field, it is highly recommended to populate it with the meaningful content. The list of possible choices is automatically populated after the original selection of the Line was made, so in 99% of cases the selection can be done easily. In the remaining 1% of cases you can actually type directly into the field, creating your very special ways of spending. In case that "very special" subcategory appears again, you can add it directly to the program Glossary, which is actually a repository for the common "Hows". See Glossary for details.
Transaction value in the original currency. Since most of the time we record expenses, and rather seldom - incomes, Tyoma's Family finance follows the accord of the credit card statement, i.e. expenses are recorded as positive numbers, while incomes are negative. Although it may seem strange at the beginning, the convenience for the data entry makes this worth while. The currency symbol is defined in Window's Regional Options.
The typical usage of the What field is a sub-sub-category, or simply a comment for an unusual, strange or suspicious transaction. There is no pre-populated value or drop-down menu. This field is normally unused, but sometimes comes really handy.
Tyoma's Family Finance uses dual-currency accounting. All transactions should have their value recorded in the original currency (either main or secondary currency), but for the purpose of calculation of overall combined wealth they have to be transformed to a common base, i.e. main system currency. When a transaction occurs in the main currency, the transaction Value is equal to the transaction Reporting value, i.e. 1:1 transformation, simple copy is performed.
Since 1:1 is a button, not a field, the tab-enter behavior is slightly different. One can normally move between fields with both Enter key and Tab key. For the button, the Enter key is an equivalent of pressing the key, while Tab is still reserved for the movement to the next entry field. For a typical transaction one navigates through the form with Enter key, performs copying of Value into Reporting field with Enter key on the 1:1 button, and continues movement to the Who group with the Tab key.
Tyoma's Family Finance assumes equal and at the same time independent financial rights and responsibilities of the spouses. Transaction can be performed by She (female partner) or by He (male partner). Certain expenses, like mortgage, or expenses related to children are joint, assuming 50-50 contributions from both spouses (partners).
This very primitive scheme allows Family Finance to handle any kind of financial relationship between spouses, without creating unneeded additional checking accounts or cash-storage-between-book-pages. In case of full trust between spouses, all income and expenses can be recorded as Both and family's finances are really joined. The financial separation between spouses can be serious, in which case spouses book their incomes in their own names, and independently contribute to various expenses. You can also imagine all intermediate arrangements, such as earning husband transfer 35% of his net income to the wife's account, making the total possible family expenses level in the Both category limited by 70% (all wife's 35% plus matching contribution from his account), spending the rest 30% to his pleasure.
The software is rather flexible, but it assumes complete trust between spouses on the income and expenses information. There is no login/password user protection and both partners can see all records of each other. If spouses want to hide something from each other, they are not acting as a family in this respect, and Tyoma's Family Finance are not for them.
In addition to the three main choices: Both (default), She and He, two additional options are available. Inc stands for the transactions by Family performed as a separate legal entity, typically, as an S-corporation. Another usage for the Inc could be a mark for tax-deductible expenses and earnings. In most cases Inc is treated similarly to Both, transactions recorded as Inc contribute 50-50 to both spouses. After filling the tax return for the fiscal year on S-corporation, family normally "clear up" the virtual Inc account by transferring the balance from Inc to Both (see transfer between accounts).
The last answer to the Who? question can be Nobody. Transactions marked as Nobody belong to neither He nor She and do not contribute to any wealth reports. We see very limited usage for the Nobody's transactions. For example, you have got funds on your banking account from your relative or friend, which must soon be returned back. It is not a good idea to record the opposite transaction, when you lend someone even for a short time, as a Nobody's transaction, because you cannot be 100% sure that you will get the money back on time. It is better to use the general section and make the lending party (She, He or Both) responsible for the transaction.
After pressing the Enter key on the last group on the form, the transaction record is saved into the database. You can return to it by pressing left arrow in the form's status bar.
As you might have noticed, the straight-forward filling out of the data entry form with the Tab/Return key bypass several fields and buttons. In order to get to those controls, one has to use a mouse, not a keyboard. Most of the bypassed features are related to entering transactions in an auxiliary or foreign currency.
Entering a foreign currency transaction starts with the selecting a foreign currency account in the From drop-down menu. There is no visible difference between foreign and main currency transactions entry until you get to the 1:1 button. For a foreign account, 1:1 button is blocked. The simplest way to calculate the Reporting value is to click on the Exchange button. The form will search for the nearest date in the Exchange rate before the transaction date and put it into Exch. Date field. After that it will retrieve the exchange rate from the Rate table and place it into the rate field. Finally, it multiplies Value by the rate and places the result into the Reporting field.
It is possible to modify all three values: Exch. date, rate and Reporting value. In practice, however, it makes sense to modify the Reporting value only, because it affects the reports. Exchange rate and date are stored in the transaction record for reference, but not used in the report preparation. If you see that the program is using a really outdated exchange rate, it is worth while to enter an appropriate exchange rate into the Rate table, after that return back to the record and press Exchange button again - Reporting value will be recalculated with the updated exchange rate.
There is a lonely check box Final in the center of the form. Sometimes one needs to record a transaction, while not all essential info is directly available. For example, you withdraw funds from your foreign checking account through an ATM. On one hand, this transaction changes statuses of your accounts, sometimes, significantly. On the other hand, you are not always able to record the withdrawal transaction accurately, because many ATMs give you the receipt with the information about the actual money received in local currency, not about the money withdrawn in foreign currency. In this case you may want to place an approximate, not a final value of the transaction and clear up the checkbox, as an indicator that you have to return back to this record again. You cannot close the period (see section Disturbance), if you have some non-finalized transaction in it in this account.
In order to see all non-finalized transactions, press the filter button. If there are no non-finalized transactions, we will see the same form with the count equal to 1, i.e. form in preparation for a new date entry. To return back to the data entry form - close it and open again.
In order to prevent duplicate entries, the system does not allow you to enter transaction in the past with the same From, Date, Line, How and Value. If you need to record similar transactions of such kind, you should make them a little bit different, for example, by shifting the transaction time by one second or so. There is no such restriction for entering transactions in the present, since you cannot enter several transactions at the same moment, hence they are always slightly different.
The button with the toilet sign deletes the current record. The cursor relocates to the first record in the open transaction set.
Finally, the Closing door button is an easy way to close the data entry form and do something else.
Unlike the traditional accounting systems, Tyoma Family Finance does not require closing of transactions for all accounts prior to a particular deadline. Moreover, there is no particular reason, but convenience, for performing period closures. Some accounts, like term deposits with little motion, can stay open for the whole year, while fast running, not always receipt documented cash accounts can be closed every week or before and after a large shopping.
Click on the Disturbance button in the Main menu to get to the Disturbance screen. The account selection can be performed by navigation buttons in the form's status bar. If the form looks complicated, it is mainly because of the messy design. Most of the fields are read-only, pre-calculated and locked. Normally, there are only two fields that need to be adjusted: "Would be" and "Actual".
Would be is the proposed period closing date. In most cases the form offers the present moment as the most reasonable value for the period closure. However, there is one hard-coded exception: when a period has one or more non-finalized transactions, the new "would be" is set up to be one second before the first non-finalized transaction. The badly designed warning "Problem at ..." under the "Would be" field gives a rough indication about the transaction preventing closure as of today. If more information about non-finalized transaction is available, one has to return back to the data entry menu and adjust the transaction in question accordingly.
Another reason to adjust the "Would be" date is when you perform a closure based on the banking or credit card statement. In this case the you normally keep the period in the Family Finance in synch with the period in the bank institution and "Would be" becomes some date in the past.
Warning: All period boundaries are exclusive. Therefore, if a transaction is booked at the same moment as the period closure happens, the transaction happens to be accounted for in neither of the two periods. In order to avoid such a problem, it is recommended to check the potential Would be date using the Why? button, before actually changing it. Another method is to set not only the date, but also the time for the closure, the risk of the two shooting into the same moment (up to seconds) in the past is negligible. In case the mistake is spotted, the wrong record can be troubleshot with GL_check query.
Type actual account balance into the Actual field. Enter key calculates the Disturbance. If disturbance is not zero, you should check the list of the contributing transactions on the account by pressing Why? button. In case you spotted some disturbance on the list, like missing/doubled/mistyped/misplaced etc. lines, you can return back to the Data Entry form and adjust transactions accordingly.
The Check query that opened by Why? button shows the subset of the complete AP table, the same table that used in date entry. It is possible, although not recommended, to adjust records directly via Check query, the changes will be written into the database as if done via Data Entry interface. Record deletion via Check query is rather safe.
When account is designated as a cash account, a long group of the individual banknotes/coins denomination are displayed instead of a single Actual field for the actual balance. Currently the program is adjusted for Euro coinage from 100 € to 1 euro cent. The list of 13 (!) denominations is not complete, though: 200, 500 and 1000 bills are seldom used in practice in regular households, and in the unlikely case you do have some of them, you are normally capable of converting them into hundreds on the spot.
People who consider counting coins below their dignity (most male do) can place only banknotes onto list and accept the difference between Total and Closed as the disturbance. People who consider counting the remaining banknotes too frustrating (most females do), can ignore cash accounts completely and record only withdraw from the bank account: "When I get cash, I spend it in no time". We do not tell you how to monitor your finances. We only give you possible options.
It is convenient to sort out banknotes/coins in descending order and use the right (numeric) part of the standard keyboard for typing. The Enter key in the right keyboard corner moves cursor from field to field as good as the main Enter key in the main keyboard area. Please make sure all fields are populated either with numbers or with zeros (default value) and not empty spaces, because empty space is not zero in MS Access notation.
Change of the main currency. In case of another reporting currency, not Euro, you may find the present denomination not corresponding directly to your currency. There are two ways of solving this problem: quick workaround or actual fixing the form.
As a quick workaround you can combine coins into something that fits. For example, I see two quarters as a single 50 cents coin, while the last quarter is 5 nickels. In case you do not bother with counting quarters, the European banknote system seems to cover the denomination of most of the major currencies, except Yen. For the Yen one should probably use 100 Yen as a monetary unit. When Tyoma's Family Finance was created, the dedicated auxiliary currency was TRR (Thousand Russian Rubles) with the exchange rate of $1 for approximately 5 TRR.
As a proper way for handling the main currency coinage, you can open the Disturbance form in design view and adjust Visual Basic formula for the Total. It takes a couple of minutes for a person with Visual Basic experience, but can be overkill for someone without programming skills.
Finally, one can remove the cash flag from the account and count cash in his valet himself. There is no serious reason for using cash flag, except the convenience for counting.
Pressing button OK performs the following actions:
Sometimes, a transaction needs to be recorded in the already closed period. In order to do so, you have to unlock the previous period, or if the transaction was performed some time ago, unlock several periods. After performing the correction, you have to redo the period(s) closure once again. Warning: unlocking previous periods does not automatically remove disturbance transactions. It is good practice to examine the old disturbance while performing re-closing and remove it before OK-ing period.
Exit
"Closing door" button closes the form and returns to the main menu.
Table Banks is the repository for the From field. It is a simple table which can be edited directly. To edit it, restore the database window in the left lower corner, select Tables in the left navigation and double click on Banks icon.
Place the cursor on the From field in the last line and type the new account name. In the next field indicate whether exchange is applicable, i.e. whether it is an account in an auxiliary currency: -1 = Yes, 0 = No. In the field cash type -1 (Yes), if account is a cash account. Responsible field is an indicator of who is responsible for disturbance on the account by default: 1= She, 2=He, 3=Inc, 4=Nobody. In most cases default value of 0=Both is appropriate. Accept default values for other fields.
You have probably noticed that there is no field for opening date. The opening date is located in the other table, called GL. Double click on icon GL and scroll to the last line in the table. In the field From select the name of the new account from the dropdown menu; in the field Date - the account opening date and accept default values for other fields. Now account is available both in Data Entry form and Disturbance form.
A typical first action for the new account is to register its current balance. The Line "general" and How "opening" are the logical choice for the accounts that have already existed for a while, but only now get registered in Tyoma's Family Finance. However, if you are really opening a new account and transfer to it some balances from the existing accounts, then the couple of "general-withdraw" from the old account and "general-deposit" to the new account is more appropriate.
Existing account can be disabled setting Active flag field in the Banks table equals to No (0). It makes no sense to disable accounts with balances, so it is recommended to perform final closure of the account first. In case accounts bear some uncollectible debts, they become the account's final "disturbance" transaction.
Never ever delete an exiting account! Deletion of an account will delete all relevant entries throughout the whole database and can seriously misplace exchange rate corrections made earlier for the auxiliary currency accounts. After all, a closed account is as good as deleted in the day-to-day life. You can delete an account without movement, for example, sample accounts provided on delivery.
You can also rename accounts, for example from "Checking -City" to "HSBC", but you would not want to do so with an account in use.
Glossary is a helper table where commonly used expense categories (Line) and subcategories (How) are recorded. Please feel free to modify the content at your convenience. Deleting/updating/Creating new entries does affect existing transactions, but is only applicable to new data. Clicking on the + sign on the left will show you the transactions in this subcategory.
Rate is another helper table dedicated to keeping history of exchange rates. In most cases you would not want to modify records in the table, because you are not in the position to change the rate's history, but just append the a current exchange rate. In case you are monitoring the exchange rate between US $ and Euro, you can take advantage of the automatic retrieval of the exchange rate from the tyoma.com server; see section "automatic rate retrieval"
Summary opens a tiny popup with the most important current info:
In case transactions by Inc have a non-zero balance, the Inc status is printed on the next line. Please note that Inc is not an independent legal entity for the Family Finance purposes, its assets (or debts) are equally distributed between She and He.
To the contrary, Nobody is considered to be an independent legal entity and its balance does not belong either to She or He and does not contribute to the overall wealth.
The last two lines give the split of the common wealth between the Investment component (Capex) and day-to-day funds (Opex). Capex plus Opex always give the Together value.
Wealth button gives the history of the Together changes in monthly intervals. It is recalculated every time the query is called, so depending upon the number of records in the database and your processor performance it can take a few seconds to run.
Status is the query quickly displaying you the balances on your accounts. Accounts are sorted in the descending order, so that you see your biggest actives first and your biggest debts last. The right column shows you the balances in the main currency, either accurate for the accounts in the main currency, or approximate, based on the most recent exchange rate for the accounts in the auxiliary currency. The sum of the right column gives the value of Together + Nobody from the Summary popup.
The middle column is an actual balance in the auxiliary currency and empty for accounts in the main currency. There must be a correspondence between the Closed field in the Disturbance form and account balances in the Status query, but this correspondence is not always held. In particular, when one changes the value in the closed period by directly accessing AP table, the change is only visible in the Status query and not visible in the Disturbance form. To troubleshoot the problem and repair the damage, see Trouble Shooting GL section.
History button opens the History form, where one can get the read-only access to the transactions in the closed period. History also gives access to the closed accounts information.
Select the account with the navigation buttons in the lower line. The account name "From" can be read in the line above. Accounts are sorted by name, closed accounts are located after active accounts.
Select the period with the navigation buttons in the third line from the bottom. The period closure date is displayed in the line above.
The main form body displays the excel-style list of transactions in the closed period in the same form as it is displayed by the Why? query in the Disturbance form.
Closing door button in the main menu closes the program.
Tyoma's Family Finance executes reasonable data protection; it is difficult to break data consistency by working with menus and the recommended query. However, the program does not enforce any security: any PC user can get a complete picture of all transactions and, moreover, with some efforts can delete and damage data. This is the price for the user's complete control over the application.
The two most typical damages are accidental: Closing the period at the moment of the transaction, so that the transaction gets stuck between two periods and does not belong to any period, and changing data directly in the AP table.
If you see a discrepancy between Status and Disturbance forms, you can start the trouble shooting with the GL_check query. Open the GL_check in Development mode and type account name in the Criteria field of the last column "From" and run the query. Your problem begins in the period where Value is not equal to SumOfValue with the opposite sign.
The easiest way is to reopen the closed period prior to the period in question and re-close them again. If the problem is well in the past, you can try adjusting the wrong entries in the AP table directly, but success of this exercise fully depends upon your data-digging skills.
Summary05 shows you a monthly breakdown of your expenses by line in familiar excel style: month number on the top, category on the left. To copy the data into Excel - click on the small square in the left top corner and copy the selection into Clipboard; pasting into Excel will keep the table structure and values. To change year - open the query in the Design mode and change the year.
Summary query shows a breakdown of expresses by line for a particular year.
Total query asks you for the year and return the expenses for the whole year.
Result shows a precise breakdown of expenses by category and subcategory for the year 2003. Adjust the period by opening query in the design view.
Same as Result for a particular year.
Income for a particular year (with minus).
Please fill free to construct your own queries.
In case you are using Euro as a primary currency and US dollar as a secondary one (or vice versa) you can take an advantage of the automatic rate retrieval from the tyoma.com website. In order to enable the retrieval, you have to install and configure ODBC driver for MySql.
| Windows DSN name: | finance_tyoma.com |
| MySQL host (name or IP): | tyoma.com |
| MySQL database name: | tyoma_com |
| User: | finance |
| Password: | rate_reader |
If everything is done right, you can click on the Rates line in the Table tab of the main Database window and see all rates from the tyoma.com server. Rates is a remote table, this is why it has a different icon: small earth globe, not a regular table sign.
In case the direct connection does not work, you can try to relink the rates table manually:
"SELECT rates.whendate, rate*0.001 AS Expr1 " & _which has to be replaced with:
"SELECT rates.whendate, 1000./rate AS Expr1 " & _Save your change and reopen the program.
Automatic rate retrieval occurs every day when you open the program. Unless today's rate is already copied, today rate, as well as the rate info the previous days when the program was in use, is quietly sucked into the rate table.
Although Tyoma's Family Finance is primitive accounting software, it clearly distinguishes between Accounts Payable (AP - Data Entry form) and General Ledger (GL - disturbance form). There was also Account Receivable (AR) ledger, but it was so similar in design and behavior to the AP that the author decided to combine them. The indicator of the transactions that belong NOT to the AP ledger, but rather to the General Ledger (and AR ledger) is general Line. Transactions in general line are not considered to be expenses and are filtered out by the most report queries.
One of the most common general transactions is the couple general-withdraw/general-deposit. Because the sum total of withdraw and deposit is always zero, it can be actually recorded in arbitrary ways, but better keep it in the "non-expense", general area.
Another very important general category is income. You can have either one generic income, or if you are lucky and have several independent sources, you can independently monitor income_full_time, income_frelance, income_brokerage, etc.
General-opening is reserved as the first transaction for the account, it should only be one general-opening per account.
General-disturbance is the machine-generated entry indicating that cash flow on the account cannot be backed by the transactions logs. General-disturbance is normally acceptable on cash accounts, but are rather alarming on the bank, credit cards, mutual funds and trading accounts, where complete log of your transactions is normally available from the service provider.
There are some differences between general-banking fee, general-tax and general-stolen, but the result is normally the same: you used to have money, which someone else took away, when you lost control over it for a split second.
With more and more banks and investment institutions moving into handling of private individuals' monthly statements by pure electronic means, the import of the transaction list into home accounting software becomes more and more popular. There is no such functionality in Tyoma's Family Finance, although nothing prevents you technically from doing so. There are two major reasons, why it has not been done.
A typical credit card statement consists of Date, descriptions and amount. Date can be easily mapped to the When field of the form, amount to the Value field and Description to the What field. From is constant, equal to the credit card name, which gives the total of 4 mappable fields. There are, however, four remaining fields to map: Line, How, Tax and Who. If the transaction involves foreign currencies, the data mapping is close to impossible, because you have to manually populate the exchange rate, date and reporting value, if this foreign currency is your auxiliary currency. If not, you have to handle the transaction in some third way.
Another reason is of more methodological nature. You would like to keep control over your family finances, hence you normally perform data entry every day or at least every week. You are doing it based primarily on your memory, supported by receipts from your wallet. You do not trust monthly credit card statements; you are auditing them against your records, absolutely the same procedure as comparing the records in the checkbook against monthly bank statements on the checking account - so customary in the XX century.
Tyoma's Family Finance is your old checkbook in your computer.