CODEWITHSUNDEEP
google-sheetspivot-table
incher91
incher91

Reputation: 27

Google Sheets Pivot Table - Show dates in between

I have a pivot table with the cumultative number of shares for each stock and with different timestamps when a portfolio-change was made (invest or divest): https://docs.google.com/spreadsheets/d/1IxdeBriRA9DgVclAWwfrz5ni1bZO94xwh0jFCghTLDg/edit?usp=sharing

Now I want to add the dates 'in between' (for every single weekday) the invest and the divest timestamps, where logically the cumultative numer of shares are more than zero.

Example 'Apple': I want the pivot table to show all the dates for every single weekday for Apple from 2013-05-08 to 2014-12-16 with the cumultative num of shares.

Example 'AT&T': Because there is no divest of AT&T Stocks, I want all the dates from 2020-04-06 to today.

I'd like to have this two examples with all the other stocks in one table to apply further functions. The purpose is to use the GOOGLEFINANCE function for the stock prices for each single day afterwards. But first, I need all the dates (where the number of shares is >0).

In the second sheet are the 'raw data'. I'm also fine if there is a better solution than using pivot table.

Thank you very much! Greets Fabian

Upvotes: 0

Views: 809

Answers (1)

Oleg_S
Oleg_S

Reputation: 161

Please, see testFile.

Since your data contains large period of time, table with all dates included for all stocks would be really big and unusable so suggest to select one stock and create table for it.

Thus on testSheet in cell B1 you select stock name you are interested in. In cell B2 select whether you want period for only historic data or up to date.

Following formula creates sequence of dates starting from first date for selected stock and up to date.

=ARRAYFORMULA(
IF($B$2="Today";
SEQUENCE(TODAY()-MIN(FILTER('Stock Track Record'!A:A;'Stock Track Record'!F:F=$B$1))+1;1;
MIN(FILTER('Stock Track Record'!A:A;'Stock Track Record'!F:F=$B$1));1)))

Then next formula gets values from your initial data.

=ARRAYFORMULA(
IFERROR(VLOOKUP($A$5:$A;
QUERY({'Stock Track Record'!$A:$F};
"Select Col1, Col2, Col3, Col4
Where Col6 = '"&$B$1&"'";0);2;0);""))

And following calculates cumulative number of shares:

=ARRAYFORMULA(IF(A5:A="";"";SUMIF(A5:A;"<="&A5:A;D5:D)))

Upvotes: 0

Related Questions