Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If I have transcation data like this: (date,spend,balance)

2013-01-01,    ,$60
2013-01-03,-$10,$50
2013-01-04,-$10,$40
2013-01-04,-$10,$30
2013-01-06,+$20,$50

How can I convert it to a list of daily balances, like this:

2013-01-01,   0,$60
2013-01-02,   0,$60
2013-01-03,-$10,$50
2013-01-04,-$20,$30
2013-01-05,   0,$30
2013-06-06,+$20,$50

Essentially I need to group all the transaction of one date together, then insert empty transactions for every date that doesn't exist.

Does excel have any functions for this?

share|improve this question
    
You're asking for a worksheet function. Would you be open to a VBA or Macro answer? How about a pivot table? What have you tried so far? –  Raystafarian Jul 27 '13 at 1:26
    
I was hoping for a non vba solution. I could do this in many languages, but I was hoping for a simple spreadsheet others can use that doesn't use macros. I have tried going through all the functions available. I can't wrap my head around how to do it with a pivot table? –  mundi Jul 27 '13 at 3:39
    
Well, there is no worksheet function to insert rows with values. You could create a dummy column of all dates and include that in the pivot table –  Raystafarian Jul 27 '13 at 9:00
add comment

1 Answer

I added every date to the date column. Then did a pivot table with date and sum of the second column, to give the change in balance every day. Balance itself can then be worked out based on any starting value.

share|improve this answer
1  
To help future visitors, can you expand on your answer? –  Canadian Luke Jul 27 '13 at 23:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.