Help needed with personal finance tool

[MoneyPlanters]

Hello,
I am trying to create a personal finance calculator which takes inputs for :
1. Datewise Income with category and
2. Datewise Income with category

Category list for both needs to be modifiable.

And then I need a dashboard where I can get answers like :

Monthly expense trends for fuel/power/transport etc
% of income spending trends for fuel/transport etc
monthwise cash balance
monthwise investments
annual dividend income
annual stock investments

Anything like this possible ?

Thanks.

 

[alansidman]

Probably possible. Would be helpful if you posted some sample data. Also, what is the difference between 1 and 2 listed in your request.

 

[MoneyPlanters]

Thanks for your reply.
Difference between 1 and 2 would be Income and Expenditure. Mistake on my post.
Sample data attached. I made two tables, one for datewise and category wise income and another for expense. Categories are setup with data validation. Cant get the pivot chart part.[TABLE="width: 650"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 650"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Expenditure[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1-Jun-14[/TD]
[TD]Conveyance[/TD]
[TD]22.00[/TD]
[/TR]
[TR]
[TD]1-Jun-14[/TD]
[TD]Grocery[/TD]
[TD]58.00[/TD]
[/TR]
[TR]
[TD]1-Jun-14[/TD]
[TD]Others[/TD]
[TD]1,500.00[/TD]
[/TR]
[TR]
[TD]2-Jun-14[/TD]
[TD]Conveyance[/TD]
[TD]89.00[/TD]
[/TR]
[TR]
[TD]2-Jun-14[/TD]
[TD]Food[/TD]
[TD]90.00[/TD]
[/TR]
[TR]
[TD]2-Jun-14[/TD]
[TD]Cigarettes[/TD]
[TD]85.00[/TD]
[/TR]
[TR]
[TD]3-Jun-14[/TD]
[TD]Cigarettes[/TD]
[TD]85.00[/TD]
[/TR]
[TR]
[TD]3-Jun-14[/TD]
[TD]Grocery[/TD]
[TD]40.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 515"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Income[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1-Jun-14[/TD]
[TD]Gifts[/TD]
[TD]3,000.00[/TD]
[/TR]
[TR]
[TD]4-Jun-14[/TD]
[TD]Interest[/TD]
[TD]325.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

I would do it like this:

One table. Make the expenses negative and the income positive. Create a pivot table and with date and category in the row area and the numbers in the column area. Group the PT on Months. This will give you a summary of all income and expenses by category by month. Once you have done this, click on the pivot chart icon and Excel will automatically create a chart for you.

 

[MoneyPlanters]

Thank you Sir. Works well.