Using Excel to summarize Credit and Debit category columns and a single value column

I'm working on a personal worksheet to summarize my adventures on the altcoins universe. I would like to keep track of:

• how much real money I deposited on each exchange
• the current altcoin amount in my wallet
• the average price of my current altcoin position
• the current balance per exchange (ie, real money laying around)

I would do something like this (working SQL fiddle here):

• Only a row per operation, identifying its ID# and when it happened;
• [Debit]/[Credit] columns would indicate where the money came from/went to;
• [Quantity] column would hold the amount of [Credit] currency
• [Value] column would always be money
• [Description] column is only for sanity sake

My ultimate goal would be have something like:

Where:

• [Blue] columns from pivot
• [Orange] column would be manually entered, or a VLOOKUP to somewhere else
• [Green] columns would be the header formula

I'm having a hard time to come with a solution because I don't what would be an equivalent for UNION in Excel, as I need to work out those credit/debit columns differently.

Can you guys point me in the right direction?

1 Answer

I replicated your data in a Table called data then:

1. Used Data>Get & Transform Data>From Table/Range to create a query called data
2. Duplicated the query created in step 1 (right-click, duplicate) to create a query called 'debit'
3. Renamed the DEBIT column to Account in the debit query
4. Removed the CREDIT column from the debit query
5. Inverted the sign of Qty and Value in the debit query
6. Added a column called Type with the value DEBIT in the debit query
7. Duplicated the data query as a query called 'credit'
8. Removed the DEBIT column from the credit query
9. Renamed the CREDIT column to Account in the credit query
10. Added a column called Type with the value CREDIT in the credit query
11. Used Home>Combine>Append Queries>Append Queries as New to append the debit and credit queries together (this achieves your UNION)
12. Renamed the new append query to DebitCredit
13. Added a custom column called Rate with this formula to the DebitCredit query: if [Type] = "CREDIT" then [Value]/[Qty] else null

The resulting query (with individual queries listed at the left):

Use Home>Close & Load to put the query back into the pivot table in the right format for pivoting and easy aggregation across accounts.

If you convert your data range to a Table using Ctrl+T and use the Table Design tab to name it data, you can use this in the Advanced Editor in the Power Query Editor to create the data query:

let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Date", type datetime}, {"Exchange", type text}, {"DEBIT", type text}, {"CREDIT", type text}, {"Qty", type number}, {"Value", Int64.Type}})
in #"Changed Type"

This to create debit (admittedly could be a little cleaner):

let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Date", type datetime}, {"Exchange", type text}, {"DEBIT", type text}, {"CREDIT", type text}, {"Qty", type number}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "type", each "DEBIT"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CREDIT"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DEBIT", "ACCOUNT"}, {"Qty", "QTY_ORIG"}, {"Value", "VALUE_ORIG"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "qty", each [QTY_ORIG]*-1), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Value", each [VALUE_ORIG]*-1), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"QTY_ORIG", "VALUE_ORIG"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Order #", "Date", "Exchange", "ACCOUNT", "qty", "Value", "type"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"qty", "Qty"}, {"type", "Type"}, {"ACCOUNT", "Account"}})
in #"Renamed Columns1"

This to create credit:

let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Date", type datetime}, {"Exchange", type text}, {"DEBIT", type text}, {"CREDIT", type text}, {"Qty", type number}, {"Value", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"DEBIT"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"CREDIT", "ACCOUNT"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Type", each "CREDIT"), #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"ACCOUNT", "Account"}})
in #"Renamed Columns1"

And this to create DebitCredit:

let Source = Table.Combine({credit, debit}), #"Added Custom" = Table.AddColumn(Source, "Rate", each if [Type] = "CREDIT" then [Value]/[Qty] else null)
in #"Added Custom"

You can read here to see how to create a query from scratch using the Advanced Editor (into which you can paste the queries above).

As you can see, the pivot table calculates correctly without much trouble:

At least I think it does! Doesn't match your table though :)

You might consider just organizing the source data with CREDIT/DEBIT as an attribute of each row and avoid some hassle.