SnowCron

.COM


SnowCron.com








Learn Touch Typing

Charting software for money tracking. Run elaborated queries against your records and present them as charts.

Contents

What is it for?

Balance is a money tracking software, it keeps records (that you are supposed to enter) about your transactions. It does not force you to follow strict standards though: you can enter data for each check you receive, or only monthly balance on your account.

The data stored internally, including: name (like "food"), amount (like 3.99), date (01/01/2015) and comment (ignored by software, but can help you to remember what it was).
We will walk through the data in the next chapter, "Import".

The data program keeps can be retrieved by name (we may want to know how much we spent on food), by date (how much do we spend per month), or both (how much do we spend for food, medicine, entertainment and so on, every month). You can create your own queries, retrieving specific data, for elaborated cases (give us monthly expences, sorted by name, in reverse order, excluding food, and only if the amount is above $100).

After we got the result of our query (which comes in a form of a table), we may want to create a chart. Three types of charts are available: Line Charts, Bar Charts and Pie Charts. This is pretty standard. But as you can build elaborated queries, you can plot charts most of personal finances software wouldn't let you to.

Finally, you can export the table you got as a result of your query; this is pretty straightforward, too.

Import.

It is possible to enter data - all your data - by typint it on Android keyboard, but if you already have it elsewhere, there is a faster way. Save the data on the SD card as a text file, and run "Import" from the Balance.

The data in that text file should be in the following format:

5	2014	food				-160
5	2014	home				-450
5	2014	food				-232    spent it by acident
5	2014	health				-56
5	2014	food				-187                
                

The first number is month. That's right, it is not possible to import day of month in the current version. It will change in future.
The second number is year. Note, that you can not use space character as a delimiter, only TAB, comma and semicolon. The reason is simple: sometimes you want more than one word as a name of the item. Like "birthday gifts".

The third column is a name of an item, the forth is the amount. The rest of the line is ignored by a program, use it if you want to store detailed description of the transaction.
Note, that you can keep both negative and positive values in the same column (like salary and expences).

To import data into the program, start the Balance, click "Import" button:

Click the elipsis ("...") button, a "Select File" dialog box will show up:

... and select the file you want to import data from, in our example it is called "money.txt":

After the file is selected, click the "Import" button. The data will be imported.

Very important! If you click the "Import" button twice, the data will be imported TWICE, which is not what you want. If you did it by accident, click "Clear all data" button and repeat importing.

To complete our "Import" chapter, let's mention the "Clear all Queries" button. Queries are chunks of text (details later in "Reports" chapter) that describe what you want to do with your data. You can add, remove or alter them, and if at some point you deside to roll back to default set of queries, click "Clear all queries". All queries will be deleted, and a default set will be restored.

Reports.

Reports tab groups up functionality to query your data and to build charts based on those queries.

On the default page of Reports, you can see the Query (text area on the left), the Report table (on the right) that holds the result of the last query, and a toolbar (on top).

Beyond doubts, Query is the most important part of this tab, so let's take a closer look on it.

Queries are text that you can edit if necessary. They determine a way you query the data you have. By default, some queries come with the program, and you can select one of them from the combo box located on the toolbar.

A simplest query looks like this:

SELECT * FROM balance

It says "select all fields (the "*") from "balance" table", and is not very useful. Plus, it is not very fast, too.

An example of a query that is simple but useful is:

SELECT name, SUM(amount) FROM balance WHERE amount > 0 GROUP BY name ORDER BY name COLLATE NOCASE ASC

It says "select the data from a field "name", plus the sum of the "amount" field, where amount > 0 (which means we want to take a look at our income, ignoring expenses). The "ORDER BY name" command will group together the data for records with the same name (salary with salary, interest with interest), so we will have one record for salery, one for interest (providing your bank pays you percents) and so on.

Translated to English, it says "calculate sums, separately for each different name in the "name" column".

The "COLLATE NOCASE ASC" part will sort records by name, alphabetically, from A to Z, case ignored.

Please note, that regardless its strangeness, SQL is a SIMPLE language, and a lot of fun, too.

SELECT name, SUM(amount) FROM balance WHERE amount > 0 GROUP BY name ORDER BY name COLLATE NOCASE ASC

Now, to disprove that last statement, let's select "By Month, Flow", as the most complex of default queries.

A query has two parts, first, a mandatory SQL code that will query the database. Balance uses SQLite database engine, and you can find literary thousands tutorials online. Regardless, we are going to explain statements we use.

The second part can be omitted, as a mater of fact, it is present only in "By Month, Flow" query. The big problem with SQL is that it can not calculate running sum, or to put it different, it can, but in the most inefficient way. A running sum is our way to, say, sum up our salary: Jan - $1000, Feb - $2000, Mar - $3000... It looks great on a chart, but building an SQL statement for it is a pain.

To solve the problem, we have an extra line in our query, starting with "Running Sum:" After the colon we list field names we want to calculate a running sum on, and the name on a column we want to get as a result of the calculation. For example, as you know, we have a column called "amount". If I want to calculate a running sum on it and to put the result in a column called "Total", I will write "Running Sum: amount, total". If I want more columns, I will add more pairs, comma separated.

Here is the "By Month, Flow" query:

SELECT date, name, Income, Expense, Total
    FROM  
        (SELECT date, SUM(MAX(amount, 0)) AS Income, SUM(MIN(amount, 0)) AS Expense, SUM(amount) AS Total
	        FROM balance WHERE 1 GROUP BY date ORDER BY date ASC COLLATE NOCASE ASC)

What it does: select (meaning, show in a resulting report) data from the "date" column, then sum of either the value of "amount" column or zero, depending what is larger. In other words, MAX(amount, 0) will give us only positive numbers, replacing the negative ones with zeros. "AS Income" means that instead of writing that ugly construct as the column's title, we will have "Income" there. As can be expected, the next column does exactly the same, but for negative values: SUM(MIN(amount, 0)) AS Expense
Then we have a sum of everything, regardless the sign (expences plus income gives us the money remaining): SUM(amount) AS Total. "FROM balance" means we still query the same "balance" table. "WHERE 1" is an SQL way to say "get everything, do not filter records". This statement can be omitted.

"GROUP BY date" works together with "sum", it allows you to calculate totals for each month separately.

Finally, "ORDER BY date ASC COLLATE NOCASE ASC" is used to sort our info by date.

Let's run our query (note that you need to have at least few records in your database, take a look to the "Import" of "Input" sections). Press the "Run" button, a green arrow on a toolbar, pointing to the right. The query will run, and the table on the right will be filled with data:

As you can see, wa have all columns we lisded after "SELECT" in our SQL, plus column from "Running Sum" section.

We are about done with "Reports" tab. Let's have a quick look on the remaining features. First of all, we have "up" and "down" arrows on a toolbar. If you click them, they will move selected query up or down in the combo box.

The button with an "X" icon deletes selected query.

"Save" button saves a query UNDER THE SAME NAME. Use it to edit an existing query.

Button "Save As" allows you to enter a new name for a query.

The last button on the toolbar is for exporting query results to a text file. It allows you to enter the name of the file (in Application's directory):

By pressing "Export" you will fill that file with data, same as in the table on the right side of a screen:

The leftmost button on a toolbar, "Mode", switches screen modes. For example, you can expand a table to occupy the entire screen:

Or switch to "Charts" mode.

Charts.

Charts tab allows you to plot your data. Charts are based on results of queries:

Balance uses three types of charts: line charts, bar charts and pie charts. Use icons on the toolbar to switch between them.

Line Chart.

To draw a chart, we need to select columns to be used for X and Y data. Click on a "Fields" button:

As you can see, we can choose one column for X axe, in our example "date", and few columns for Y axe (Y values). You can only use numbers and dates in Line Charts, text fields can not be used.

In addition to available fields, you can selecr "Record number" as X value, as expected, numbers are 1, 2, 3, 4...

Click "Chart", to see the Line Chart:

Bar Charts

Unlike Line Charts, Bar Charts can only use Record Number as the value for X-axe. Instead, if you choose anything but "Record Number" for X-axe:

An extra Line Chart will be added to a Bar Chart:

Use "Marker" and "Labels" icons (rightmost two icons on a toolbar) to turn on and off markers on a Line Chart and Labels with legengs for charts.

Pie Charts.

Pie (Donught) charts use one column, and an extra (non-mandatory) column as source of legends. For example, you can select SUM column to plot and "names" columns as legends.

It is also possible to see either percents of numbers, switch between the two using "Percent" icon on a toolbar:

Let's take a look at our Expences, by selecting and running a "By Name, Negative" query:

Now switch to "Charts" tab and select "name" for legends and "Total" for data:

As you can see, there is such thing as too much info :) To make it better, switch off labels:

Or switch to a full screen mode (press "Mode").

Input.

"Input" is simply a text editor:

You can use standard Android text editor to enter data:

Use semicolon as a separator.

There is a reason for not using days in dates: an average Balance user is looking for monthly information, as daily numbers are too volatile.


Download from Google Play


(C) snowcron.com, all rights reserved

Please read the disclaimer