I have categorized expenses in multiple currencies that I would like to display neatly in a pivot table. I have each transaction's value in a local currency and in USD. The goal is to show the sum of spending per category per month in USD, but also show spending in each currency in the same column.
Ideally, the PivotTable would show up as below. The category totals are the sum of spending in USD, whereas the currency totals are the sum of spending in that currency. So the category total deliberately does not match the currency total (i.e. $90 <> 20 + 64).
May Jun
Phone $ 90.00 $ 253.10
EUR € 20.00 € 7.00
USD $ 64.00 $244.00
Food $ 817.80 $ 196.14
EUR € 500.00 € 95.00
USD $ 43.00 $ 43.00
GBP £ 80.00 £ 19.00
I understand that I could have two columns per month, one in USD and another in the local currency. But this would take up too much space in my spreadsheet.
The above output is based on the following data:
Date Category USDAmt LocalAmt Currency
5/1/2013 Food 14.00 14 USD
5/1/2013 Food 29.00 29 USD
5/1/2013 Food 650.00 500 EUR
5/1/2013 Food 78.00 50 GBP
5/3/2013 Phone 49.00 49 USD
5/3/2013 Food 46.80 30 GBP
5/15/2013 Phone 26.00 20 EUR
5/20/2013 Phone 15.00 15 USD
6/2/2013 Food 22.00 22 USD
6/3/2013 Food 21.00 21 USD
6/3/2013 Food 123.50 95 EUR
6/4/2013 Food 12.48 8 GBP
6/10/2013 Phone 230.00 230 USD
6/11/2013 Food 17.16 11 GBP
6/13/2013 Phone 9.10 7 EUR
6/13/2013 Phone 14.00 14 USD
I'm open to suggestions on other ways to nearly present the data and am happy to program whatever is needed in VBA.
