Personal Finance Web

Posted on Apr 12, 2010

I’ve got another project in mind to solve a personal problem.

The Problem

I bank online and use online billpay through my bank for most of my bills. I occasionally write checks. I currently use a spreadsheet to manage my budget because I set up my budget biweekly (when I receive a paycheck) rather than monthly. I want a way to keep an eye on my budget biweekly, but am currently doing that through a spreadsheet.

I have used Quicken in the past and though I am familiar with the layout and like the easy categorization, I don’t want to be locked down to one PC to do or check my budget. So stand-alone softwares are out for me.

There are several online options: Mint.com, Quicken.com, Yodlee.com. I’ve tried Quicken.com and don’t like the limitations. For example, you can have custom categories, but they can’t be nested. I like to have a few large categories with sub-categories. Also, there are a lot of online complaints about the bank connections failing, Quicken losing data, Quicken restoring old data, etc. Mint.com and Quicken are now owned by the same company. No thanks. I just recently tried Yodlee.com and had some connection problems, though not as bad as Quicken. My first big hurdle is that I could only download about 20 transactions from my bank. (The default on my bank page.) There is nowhere to change that, so I would have to go manually update the page every couple of days. I don’t like that. I also ran into a few limitations that bothered me personally.

I searched out there for code and tutorials to create what I wanted without having to build it from scratch. I didn’t find what I needed.

Work List

Plan User Steps
Decide on Technologies
Plan Database Structure
Learn Necessary Technologies (I may need to do more of this later)
Write MySQL to create database, and preload basic categories and some dummy data
Create Main Overview Page, implementing ExtJS “Live Group Summary” into the center panel and loading data from database
Add the abilities to upload, edit, create, and view different things one at a time, testing as I go
Add login feature

Planning

So, I’m a programmer. If I want something and can’t find it out there anywhere, I’ll just make it myself.

User Steps

The first part of my design process is to decide what steps I want to allow for a user.

Log In
Create / Edit Categories
Create / Edit Budget
Add regularly scheduled payments / incomes (List or Calendar View)
Upload Register entries – assume set format for now
View / Edit Register entries (can have duplicates)
View Overview (Live Summary) Allow a view change for biweekly and monthly
Log Out

Technology

Note: I’m using an old download of [extJS Script] from the time that it was still Open Source. This is now a $700 software and offers support. I think you can still jump through hoops and get an Open-Source License for this software, but you can’t use it for anything you sell.

Overall Layout – I will be using [extJS Script] for the main layout of the site. I will be using the “Complex Layout” from the ExtJS samples. This has configurable 5-pc sections. Center, North, South, East, and West. Center has tabs so that there can be endless possibilities. North is for the headers and menus. I plan to use West for Navigation and Options. I’m not using East or South.
Log In – I have a tutorial on here for making a login for a webpage. For now, that’s all I’m doing. Later, I may upgrade it to allow multiple users, but at this time it is intended for personal use. The login screen will be a basic screen for now.
Register – The register is like the register in your checkbook. This is where you can view and edit your debits and credits. It will be done using ExtJS”Editor Grid”
Overview – This will be a page with the list of categories, sub-categories, and register entries entered in those subcategories. The page will have tons of $ subtotals. I’m using ExtJS”Live Group Summary” for this.
Editing – Most editing will be done in Pop-up windows. Using ExtJS.
Uploading CSV – I’ll need to look online for a solution for this.
Data – The data will be stored in my MySql database.
Web – The website will be hosted on my GoDaddy deluxe (paid) Linux hosting account.

Data

My data will be as follows:

Users – for now this will be hardcoded. I’ll add a user database table later.
Categories – There can be a main category and one level of sub-category. Example categories include: Home, Home-Mortgage, Home-Repairs, etc. There will need to be a MainCategory, SubCategory, and CatRelationship table.
Budget – For each lowest level of category, the user can set a budget amount. For example, the user can’t set an overall Home category budget, because that number will be determined by the total for all of the sub-categories. There must be a budget payee, date, amount, and frequency just like in scheduled transactions.
Register – This has to have (at minimum) the same structure as the .csv (Comma Separated Value) file that will be uploaded. I’m doing this with the layout that my bank sends:
- Date,Check#,Transaction Type,Description,Debits (-),Credits(+),Balance – Note here that the (-) and (+) simply means that if the item is a debit that the amount will be a negative amount. The description here is the Payee. I will not store the balance.
- Additional Data – Category. I will include a category field. When the user selects it in the UI, they will only be able to choose from categories that are in the database. There’s no cleanup for old categories. See the example categories above.
Scheduled Transactions – This has to contain the Payee (Or Payer if it’s an income), the amount, the due date, and the frequency. If this is a biweekly paycheck, or if you grocery shop once a week, or your mortgage is due once a month.

I created a database on my servers. The table creation and dummy data SQL file can be downloaded or viewed on the [cref financial-database-table-creation-tutorial] Post.

If you don’t know how to upload an SQL command file to your database and you’re using GoDaddy, see the [cref database-tutorial] Post.

Learning

Upload CSV files

The first question I chose to tackle was how to upload CSV files to the database. I know I could use PHP to upload the file and the fopen() command to open the CSV file. I could then parse it and send the data to the database line-by-line. But I’m assuming that someone out there has written the code for this. Why should I do it from scratch. So the search begins…

While googling around, I found one potential problem. While the date in my CSV will be in the format MM/DD/YYYY, I need to make sure I convert it to the date format preferred by MySQL: YYYY-MM-DD. That’s not difficult if I do it myself. Rather than waste more time digging online, I’ll just make my own and post it. 😉 I’ll put a link here when I get that source code done.

ExtJS – Complex Layout

I’ve already been looking at the samples. The best way to do this that I’ve found was to upload the whole ExtJS folder to my server and browse around in it. I found that I like the “Complex Layout” example for the main content of my page. Here are the steps I followed:

Locate the example in the file structure: ext-2.0.2\examples\layout\complex.html.
Copy that file to your web directory, then open it in the editor of your choice. See my [cref text-editors] Post. My favorite is Komodo Edit.
Start working your way through the file. Every time you see a directory reference, locate the needed file and copy it into the directory structure of your choice in your web directory. Then change the reference in the HTML file to reflect your new directory. For example, the original CSS file was located in ../../resources/css/ext-all.css. I changed it to css/ext-all.css. Don’t worry about anything else in the file at this time. Just the files. When you copy files, make sure you copy all of the shared icons to your directory. You never know when those will come in handy. Save complex.html when you’re done. Here are the exact lines I changed:
```
[code]
href="../../resources/css/ext-all.css" CHANGED TO href="resources/css/ext-all.css"
[/code]
[code]
src="../../adapter/ext/ext-base.js" CHANGED TO src="adapter/ext/ext-base.js"
[/code]
[code]
src="../../ext-all.js" CHANGED TO src="ext-all.js"
[/code]
[code]
url(../shared/icons/fam/folder_wrench.png) CHANGED TO url(shared/icons/fam/folder_wrench.png)
[/code]
[code]
url(../shared/icons/fam/folder_go.png) CHANGED TOurl(shared/icons/fam/folder_go.png)
[/code]
[code]
src="../examples.js" CHANGED TO src="examples.js"
[/code]
```
Clearly, these changes require you to copy the following folders and files into your root web directory:
- ext-2.0.2/resources
- ext-2.0.2/adapter
- ext-2.0.2/ext-all.js
- ext-2.0.2/examples/shared
- ext-2.0.2/examples/examples.js
At this point you will want to check those .js files to see if there are any directory changes necessary in there. In this case,t here is no change necessary to ext-all.js, but you will need to change the following in examples.js:
```
[code lang="HTML"]
Ext.BLANK_IMAGE_URL = '../../resources/images/default/s.gif'; CHANGED TO Ext.BLANK_IMAGE_URL = 'resources/images/default/s.gif';
[/code]
```
There are a few more directories that I copied to my root web directory:
- ext-2.0.2/examples/core
- ext-2.0.2/examples/dependency
- ext-2.0.2/examples/locale
You should now have 3 files and 5 directories in your root web directory. FTP those to your web server and open complex.html in your browser. Everything should work properly.

Now, we edit for content. I wanted to only use the North, West, and Center portions of the layout. In the complex.html file, I recommend that you comment out the javascript region from

[code]
{
                    region:'south',
[/code]

to the end of the region of

[code]
                    region:'east',
                    ...
                    ...
                                    "borderWidth": 1
                                }
                            })]
                        })
                 },
[/code]

Then comment out the last div:

[code lang="HTML"]


     south - generally for informational stuff, also could be for status bar


[/code]

Upload your changes and test that the right and bottom panels are gone and that the other panels are functioning properly.
You can now go into the other divs and remove the paragraphs and put in your own content. I also changed the following in the javascript script:
```
[code]
region:'west',
id:'west-panel',
title:'West',
[/code]
```
by changing the title:”West” to “Actions Panel”.

ExtJS – Live Group Summary

After looking at the samples, I decided that the ExtJS “Live Group Summary” Example would be a great way to display the Overview data in my webpage. This part took a lot longer as I had to learn some things to get the effect that I wanted. But here are the steps to follow to get the desired effect.

Go to ext-2.0.2\examples\grid\ and copy the following files to your root directory:
- totals.html
- totals.js
- summary.css
- GroupSummary.js
- grid-examples.css
Rename complex.html to index.html.
Open index.html and totals.html. Copy the following from totals.html into the same location in the header in index.html.
```
[code lang="html"]





[/code]
```
In totals.html, look for the header: style type=”text/css”. Copy all of the CSS data into index.html underneath the .nav entry. Tab appropriately to get the indentions looking good.
You’re now done with totals.html. Delete it from your directory.
Remove everything in the “center2” div like so:
```
[code lang="html"]
  
  
[/code]
```
Save index.html and close it. Open grid-examples.css and change the ../../resources to resources. Resources is no longer 2 directories up.

Save grid-examples.css and close it. Open totals.js. Change the section (Line 125) beginning with “frame” to this:

[code lang="javascript"]
        frame:false,
        autoWidth: true,
        autoHeight : true,
        clicksToEdit: 1,
        collapsible: false,
        animCollapse: false,
        trackMouseOver: false,
        //enableColumnMove: false,
        title: 'Sponsored Projects',
        iconCls: 'icon-grid',
        renderTo: center2
    });
[/code]

Save totals.js and upload all the new and changed code to the server for testing.

JSON – Database Communications

The best way to do the database stuff is through the JSON technology. JSON is short for JavaScript Object Notation. JSON is human-readable. It was derived from the JavaScript language, but it is language-independent. There are parsers available for virtually every programming language. Connect to the main JSON website HERE.

Learning – I found a nice forum page with an example on the ExtJS Forums page. And another one HERE.