Numbers template using .csv for Income-Expenses?

Hi Susan,

Your second question,

Isn't there any "undo"...only when you revert to a previous save?

Menu > Edit > command z will undo the last action. This is available in all Apple apps.

Redo is shift command z.

Once you have saved a document, autosave cuts in and you can Menu > File > Revert To

Then Time Machine backups😉

Regards,

Ian

Hi Ratsneve,

I dowloaded a CSV form my bank. I have edited to shorten it and hide my personal details.

Here it is in Numbers.

Column D is my bank balance.

Step 1.

The first thing I notice is that Row 1 is a Header Row. No, it should not be. It is the first row of data (transactions)

Select Row 1 and in the contextual menu, add Header Row Above

Type in the "proper"column labels

Now convert the "false" Header row 2 to a Body Row (Header Rows > 1)

Step 2.

My bank insists on placing the plus sign "+" in front of income. Numbers interprets that as Text, not a number.

To get rid of the "+" signs, insert another column (C)

Formula in new Column C (C2 and Fill Down)

=IF(LEFT(B2,1)="+",VALUE(RIGHT(B2,LEN(B2)−1)),B2)

Column C is formatted as Currency (Format Panel > Cell > Data Format > Currency)

To "fix" the values in Column C, select and copy Column C, then Menu >Edit > Paste Formula Results.

Now you can delete Column B.

I am still thinking how best to Save As Template, but it is late here.

Regards,

Ian.

Hi Susan,

With no plus signs, you do not need step 2 and it becomes easier.

Formula in C2 (and Fill Down to the last Body Cell, C6 in this example)

=IF(B2>0,B2,"")

If a value in B is >0, insert that value, else insert "" (NULL)

Formula in D2 (and Fill Down) =IF(B2<0,B2,"")

To get the totals,

Formula in Footer Cell C7 =SUM(C)

Formula in Footer Cell D7 =SUM(D)

The Balance column (F) will depend upon the starting balance on your statement.

Take a look at Template > Chooser > Personal Finance > Personal Budget.

Happy Numbering!

Ian.