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.

Expenditure Date Description Amount 1-Jun-14 Conveyance 22.00 1-Jun-14 Grocery 58.00 1-Jun-14 Others 1,500.00 2-Jun-14 Conveyance 89.00 2-Jun-14 Food 90.00 2-Jun-14 Cigarettes 85.00 3-Jun-14 Cigarettes 85.00 3-Jun-14 Grocery 40.00 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Income
Date	Description	Amount
1-Jun-14	Gifts	3,000.00
4-Jun-14	Interest	325.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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.