Ledger to pivot table
I have a pivot table where I keep track some investment activity. It is organized in a ledger format:
Date Asset Movement Value
10-Jan-18 Cash Initial 5000
15-Jan-18 GE Buy 500
20-Feb-18 GE Buy 800
25-Feb-18 GE Dividend 50
15-Mar-18 FB Buy 1000
15-Mar-18 GE Mark2Market 1500
I want to create a non-macro/VBA/GoogleAppsScript pivot table that would look like this:
Asset 10-jan-18 15-jan-18 20-feb-18 25-feb-18 15-mar-18
Cash 5000 4500 3700 3750 2750
GE 0 500 1300 1300 1500
FB 0 0 0 0 1000
Total 5000 5000 5000 5050 5250
I have tried to build this using auxiliary columns, for the initial table, that calculate the "Cash Flow" and "Asset Flow", that multiply the value using the following auxiliary table.
Movement Cash Asset
Initial 1 0
Mark2Market 0 1
Dividend 1 0
Buy -1 1
Sell 1 -1
However, those fields do not enable the pivot table to calculate the cash balance on dates on which there is no movement on the cash account. What should be the right spreadsheet architecture to make this work?
excel google-sheets
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
add a comment |
I have a pivot table where I keep track some investment activity. It is organized in a ledger format:
Date Asset Movement Value
10-Jan-18 Cash Initial 5000
15-Jan-18 GE Buy 500
20-Feb-18 GE Buy 800
25-Feb-18 GE Dividend 50
15-Mar-18 FB Buy 1000
15-Mar-18 GE Mark2Market 1500
I want to create a non-macro/VBA/GoogleAppsScript pivot table that would look like this:
Asset 10-jan-18 15-jan-18 20-feb-18 25-feb-18 15-mar-18
Cash 5000 4500 3700 3750 2750
GE 0 500 1300 1300 1500
FB 0 0 0 0 1000
Total 5000 5000 5000 5050 5250
I have tried to build this using auxiliary columns, for the initial table, that calculate the "Cash Flow" and "Asset Flow", that multiply the value using the following auxiliary table.
Movement Cash Asset
Initial 1 0
Mark2Market 0 1
Dividend 1 0
Buy -1 1
Sell 1 -1
However, those fields do not enable the pivot table to calculate the cash balance on dates on which there is no movement on the cash account. What should be the right spreadsheet architecture to make this work?
excel google-sheets
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
This could be on-topic on Super User for Excel and on Web Applications for Google Sheets, but it looks to be off-topic here because it's not clear how it's related to programming.
– Rubén
Nov 21 '18 at 0:17
Thanks for the feedback and sorry for posting it on the wrong place. Would you be able to move it to web applications? I can't do it by myself.
– Oren Pinsky
Nov 21 '18 at 8:14
add a comment |
I have a pivot table where I keep track some investment activity. It is organized in a ledger format:
Date Asset Movement Value
10-Jan-18 Cash Initial 5000
15-Jan-18 GE Buy 500
20-Feb-18 GE Buy 800
25-Feb-18 GE Dividend 50
15-Mar-18 FB Buy 1000
15-Mar-18 GE Mark2Market 1500
I want to create a non-macro/VBA/GoogleAppsScript pivot table that would look like this:
Asset 10-jan-18 15-jan-18 20-feb-18 25-feb-18 15-mar-18
Cash 5000 4500 3700 3750 2750
GE 0 500 1300 1300 1500
FB 0 0 0 0 1000
Total 5000 5000 5000 5050 5250
I have tried to build this using auxiliary columns, for the initial table, that calculate the "Cash Flow" and "Asset Flow", that multiply the value using the following auxiliary table.
Movement Cash Asset
Initial 1 0
Mark2Market 0 1
Dividend 1 0
Buy -1 1
Sell 1 -1
However, those fields do not enable the pivot table to calculate the cash balance on dates on which there is no movement on the cash account. What should be the right spreadsheet architecture to make this work?
excel google-sheets
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
I have a pivot table where I keep track some investment activity. It is organized in a ledger format:
Date Asset Movement Value
10-Jan-18 Cash Initial 5000
15-Jan-18 GE Buy 500
20-Feb-18 GE Buy 800
25-Feb-18 GE Dividend 50
15-Mar-18 FB Buy 1000
15-Mar-18 GE Mark2Market 1500
I want to create a non-macro/VBA/GoogleAppsScript pivot table that would look like this:
Asset 10-jan-18 15-jan-18 20-feb-18 25-feb-18 15-mar-18
Cash 5000 4500 3700 3750 2750
GE 0 500 1300 1300 1500
FB 0 0 0 0 1000
Total 5000 5000 5000 5050 5250
I have tried to build this using auxiliary columns, for the initial table, that calculate the "Cash Flow" and "Asset Flow", that multiply the value using the following auxiliary table.
Movement Cash Asset
Initial 1 0
Mark2Market 0 1
Dividend 1 0
Buy -1 1
Sell 1 -1
However, those fields do not enable the pivot table to calculate the cash balance on dates on which there is no movement on the cash account. What should be the right spreadsheet architecture to make this work?
excel google-sheets
excel google-sheets
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
asked Nov 20 '18 at 16:20
Oren PinskyOren Pinsky
1349
1349
This could be on-topic on Super User for Excel and on Web Applications for Google Sheets, but it looks to be off-topic here because it's not clear how it's related to programming.
– Rubén
Nov 21 '18 at 0:17
Thanks for the feedback and sorry for posting it on the wrong place. Would you be able to move it to web applications? I can't do it by myself.
– Oren Pinsky
Nov 21 '18 at 8:14
add a comment |
This could be on-topic on Super User for Excel and on Web Applications for Google Sheets, but it looks to be off-topic here because it's not clear how it's related to programming.
– Rubén
Nov 21 '18 at 0:17
Thanks for the feedback and sorry for posting it on the wrong place. Would you be able to move it to web applications? I can't do it by myself.
– Oren Pinsky
Nov 21 '18 at 8:14
This could be on-topic on Super User for Excel and on Web Applications for Google Sheets, but it looks to be off-topic here because it's not clear how it's related to programming.
– Rubén
Nov 21 '18 at 0:17
This could be on-topic on Super User for Excel and on Web Applications for Google Sheets, but it looks to be off-topic here because it's not clear how it's related to programming.
– Rubén
Nov 21 '18 at 0:17
Thanks for the feedback and sorry for posting it on the wrong place. Would you be able to move it to web applications? I can't do it by myself.
– Oren Pinsky
Nov 21 '18 at 8:14
Thanks for the feedback and sorry for posting it on the wrong place. Would you be able to move it to web applications? I can't do it by myself.
– Oren Pinsky
Nov 21 '18 at 8:14
add a comment |
1 Answer
1
active
oldest
votes
With time, I've solved my own question using the steps described below:
On the first, ledger spreadsheet, I've added columns Cash Flow, Asset Flow, Cash Balance and AssetBalance. CashFlow and AssetFlow multiply the Value column by the correspondent Movement table, using vlookup. CashBalance and AssetBalance sums up all CashFlow and AssetFlow columns, respectively, whose date is less than or equal to data of the transaction, and that have the same Asset name.
Then, on a separate spreadsheet, I do a pivot table using Google Apps Script query command:
=query({query(Ledger!A:K,"select A, B, C, D, sum(K) where D != 'Cash' group by A, B, C, D",1);
query(Ledger!A:K,"select A, B, '!Cash', 'Cash', min(J) group by A, B",1)},
"select Col2, Col3, Col4, sum(Col5) where Col2 != 'Institution' and Col2 != '' group by Col2, Col3, Col4 pivot Col1")
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397253%2fledger-to-pivot-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
With time, I've solved my own question using the steps described below:
On the first, ledger spreadsheet, I've added columns Cash Flow, Asset Flow, Cash Balance and AssetBalance. CashFlow and AssetFlow multiply the Value column by the correspondent Movement table, using vlookup. CashBalance and AssetBalance sums up all CashFlow and AssetFlow columns, respectively, whose date is less than or equal to data of the transaction, and that have the same Asset name.
Then, on a separate spreadsheet, I do a pivot table using Google Apps Script query command:
=query({query(Ledger!A:K,"select A, B, C, D, sum(K) where D != 'Cash' group by A, B, C, D",1);
query(Ledger!A:K,"select A, B, '!Cash', 'Cash', min(J) group by A, B",1)},
"select Col2, Col3, Col4, sum(Col5) where Col2 != 'Institution' and Col2 != '' group by Col2, Col3, Col4 pivot Col1")
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
add a comment |
With time, I've solved my own question using the steps described below:
On the first, ledger spreadsheet, I've added columns Cash Flow, Asset Flow, Cash Balance and AssetBalance. CashFlow and AssetFlow multiply the Value column by the correspondent Movement table, using vlookup. CashBalance and AssetBalance sums up all CashFlow and AssetFlow columns, respectively, whose date is less than or equal to data of the transaction, and that have the same Asset name.
Then, on a separate spreadsheet, I do a pivot table using Google Apps Script query command:
=query({query(Ledger!A:K,"select A, B, C, D, sum(K) where D != 'Cash' group by A, B, C, D",1);
query(Ledger!A:K,"select A, B, '!Cash', 'Cash', min(J) group by A, B",1)},
"select Col2, Col3, Col4, sum(Col5) where Col2 != 'Institution' and Col2 != '' group by Col2, Col3, Col4 pivot Col1")
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
add a comment |
With time, I've solved my own question using the steps described below:
On the first, ledger spreadsheet, I've added columns Cash Flow, Asset Flow, Cash Balance and AssetBalance. CashFlow and AssetFlow multiply the Value column by the correspondent Movement table, using vlookup. CashBalance and AssetBalance sums up all CashFlow and AssetFlow columns, respectively, whose date is less than or equal to data of the transaction, and that have the same Asset name.
Then, on a separate spreadsheet, I do a pivot table using Google Apps Script query command:
=query({query(Ledger!A:K,"select A, B, C, D, sum(K) where D != 'Cash' group by A, B, C, D",1);
query(Ledger!A:K,"select A, B, '!Cash', 'Cash', min(J) group by A, B",1)},
"select Col2, Col3, Col4, sum(Col5) where Col2 != 'Institution' and Col2 != '' group by Col2, Col3, Col4 pivot Col1")
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
With time, I've solved my own question using the steps described below:
On the first, ledger spreadsheet, I've added columns Cash Flow, Asset Flow, Cash Balance and AssetBalance. CashFlow and AssetFlow multiply the Value column by the correspondent Movement table, using vlookup. CashBalance and AssetBalance sums up all CashFlow and AssetFlow columns, respectively, whose date is less than or equal to data of the transaction, and that have the same Asset name.
Then, on a separate spreadsheet, I do a pivot table using Google Apps Script query command:
=query({query(Ledger!A:K,"select A, B, C, D, sum(K) where D != 'Cash' group by A, B, C, D",1);
query(Ledger!A:K,"select A, B, '!Cash', 'Cash', min(J) group by A, B",1)},
"select Col2, Col3, Col4, sum(Col5) where Col2 != 'Institution' and Col2 != '' group by Col2, Col3, Col4 pivot Col1")
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
answered Nov 26 '18 at 12:46
Oren PinskyOren Pinsky
1349
1349
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397253%2fledger-to-pivot-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
This could be on-topic on Super User for Excel and on Web Applications for Google Sheets, but it looks to be off-topic here because it's not clear how it's related to programming.
– Rubén
Nov 21 '18 at 0:17
Thanks for the feedback and sorry for posting it on the wrong place. Would you be able to move it to web applications? I can't do it by myself.
– Oren Pinsky
Nov 21 '18 at 8:14