Personal Expenses Tracker

Dear Friends,

For a longgggggg time, I am thinking about publishing this post. This is useful for home makers.

This excel book allows her to record the daily expenses in a sheet and summary of the expenses will be updated in a separate sheet automatically.

OK now I will start the technical details. There are two sheets in this workbook

1. Transactions (to enter the daily expenses)
   
2. Summary (to generate summary report)
   

The Transactions sheet has the following columns

No

Date

Account

Amount (SGD)

Narration

And the summary sheet the following columns

Account

Amount

Now I will explain how the solution is implemented. I have created the following range names

• ListOfAccounts
  
• TxnAccount
  
• TxnAmount
  

In the Transactions sheet, I will enter the transactions as it occurs.

Column	Description
Account	I used Data Validatation for a Combo box selection.
No	used a formula to generate number series (=A2+1). While entering the daily expenses I will not type the number first. After completing entry of all expenses, I will double click on the last number.
Amount	User Data Entry
Narration	User Data Entry

In the Summary sheet, the summary will be updated automatically

Column	Description
Account	Usually it is Permanent List. New account heads are added here only. Since the ListOfAccounts rangename points this list, any new addition here will be reflected automatically in the Transaction sheet, Account Column
Amount	Total Amount for the account head will be updated here. I used SUMIF formula for this purpose. =SUMIF(TxnAccount,A2,TxnAmount)

I know that this pretty simple yet powerful (since used by powerful homemakers!!!)

I Assure you my support. Never hesitate to drop a mail to me at karthiganesh@yahoo.com

Mailing Labels using EXCEL

Mailing Labels using EXCEL


Here is a simple tool for generating mailing labels using EXCEL.

There will be two sheets namely

1. List sheet


(larger images are given below)







2. Labels Sheet


(larger images are given below)







In the List sheet we have to enter the address information.

In the Label sheet, we have to prepare the labels using INDEX function. The following formula is used to generate labels

To bring the name in the first row =INDEX(Address,D1,2)
To bring the addr1 in the second row =INDEX(Address,D1,3)
To bring the addr2 in the third row =INDEX(Address,D1,4)
To bring the city in the fourth row =INDEX(Address,D1,5)
To bring the pincode in the fifth row ="PIN:"&INDEX(Address,D1,6)

for second column, we can add 1 to the D1 cell
for third column, we can add 2 to the D1 cell

for the second row of labels, we can use D7 cell as row reference

We can enter the formula once for first row of the labels.
For subsequent rows, we can copy and paste the formula.

I hope this simple solution will be useful for you all.

Cheers! (mail me your comments)

karthiganesh@yahoo.com

Larger images of the above sheets are given below:

List Sheet



Labels Sheet

EXCEL-VBA Application Development Strategy

As an EXCEL-VBA based BI Consultant, I faced many situation where the requirements are legacy system. There is no ready made, built-in solution available. Hence the Business Analyst and System Analyst have to find out a solution on their own. As a third part consultant, I too was asked any suitable strategy. With the experiences I gained during those many brain storming sessions, I learned many feasible, practical solution strategy. I would like to share them with you!

1. Simple Switch Board

We can have the first sheet as Main Menu. (Recently User Interface specialists call this as SWITCHBOARD). The main menu can be

• set of carefully arranged command buttons
• Custom menu (menu added to EXCEL menu)
• Custom tool bar (new toolbar added to existing toolbars)

2. Data Entry

Data Entry can be achieved with the help of UserForm or specially designed excel worksheet.

3. Data Source

The data source can be anything including

• text file
• another worksheet
• user data entry
• ORACLE, SQL Server, mySql (ODBC)

4. Output

The output of your EXCEL-VBA application can be

• another workbook
• chart
• email
• word document
• text file

I will continue other aspects of EXCEL-VBA Application Development Strategy in next blog.

We will be benefited from sharing of our knowledge. So please feel free to share your valuable knowledge with me by your comments/email etc.

Cheers!

Contact me @ karthiganesh@yahoo.com

Mail Merge utility!!!

We all know that MS-Word has wonderful feature called MailMerge for merging a Letter with many address and print individual letters. The same functionality can be implemented in EXCEL. The new EXCEL Mail Merge workbook can found new uses!!!

• A Bill / Receipt
• A Data Fillup Form
• An Account Voucher
• etc

In the simplest form, the workbook will have 3 sheets

1. LETTER: Where you will type the Letter
2. LIST: The list of addresses (or data to be printed)

Now the question is how can we view the Printing Page. I may suggest two ways:

1. View Individuals Letters/Forms
2. View Group of Letters/Forms

I amusing the INDEX function to fetch the respective data from LIST sheet into PRINTPAGE sheet by referring the sequence number in the list. You can use VLOOKUP function to fetch data based on NAME or ID field.

A bigger images of those two sheets are follow :