Create an area chart from stock values

[stefanaalten]

Hope the MrExcel community is feeling clever 'cos I'm not! I'd appreciate some help with this "simple" question! Oh, and Best Wishes for 2025.

I have data representing stocks: stock name, units held, price, value, cost, gain/loss, date (as at).

I can't modify the source data - it comes pre-supplied (daily download), so I just copy the values (insert rows) into a summary spreadsheet, as shown below (simplified example).

My question consists of two parts - hope that's okay.

1) I'd like to have a pivot table that shows at a glance how stocks have performed. As can be seen below, the data I have already provides "total as at date" (e.g. value) but the Σ Values panel insists of applying a formula ("Summarize value field by"), "sum" by default, which is not what I want. Is there a a way of saying "just show the data as is - no need to "summarize"? Hope I've explained that okay.

2) Then, and this is my main question really, I would like to produce an area chart (or something similar) that shows how each stock has contributed to the total, i.e. time along the horizontal, axis, £ along the vertical, and each "area" representing a different stock (in different colour for each).

I will have new data to add every day, i.e. another lot of stocks (these may vary over time).

Any help very much appreciated!

Here's my data

Account Summary (to illustrate question).xlsx
	A	B	C	D	E	F	G	H
1	Stock	Units held	Price (pence)	Value (£)	Cost (£)	Gain/loss (£)	Gain/loss (%)	As at (date2)
2	Stock A	1,282.36	385.37	4,941.81	4,855.16	86.65	1.78	2024-12-12
3	Stock A	1,282.36	385.66	4,945.53	4,855.16	90.37	1.86	2024-12-13
4	Stock A	1,282.36	385.83	4,947.71	4,855.16	92.55	1.91	2024-12-17
5	Stock A	1,282.36	385.51	4,943.61	4,855.16	88.45	1.82	2024-12-18
6	Stock A	1,282.36	384.55	4,931.30	4,855.16	76.14	1.57	2024-12-20
7	Stock A	1,282.36	385.19	4,939.50	4,855.16	84.34	1.74	2024-12-27
8	Stock A	1,282.36	385.59	4,944.63	4,855.16	89.47	1.84	2025-01-01
9	Stock B	680.39	2404.28	16,358.46	16,250.00	108.46	0.67	2024-12-12
10	Stock B	680.39	2397.34	16,311.24	16,250.00	61.24	0.38	2024-12-13
11	Stock B	680.39	2378.74	16,184.69	16,250.00	-65.31	-0.40	2024-12-17
12	Stock B	680.39	2353.88	16,015.54	16,250.00	-234.46	-1.44	2024-12-18
13	Stock B	680.39	2315.11	15,751.75	16,250.00	-498.25	-3.07	2024-12-20
14	Stock B	680.39	2335.66	15,891.57	16,250.00	-358.43	-2.21	2024-12-27
15	Stock B	680.39	2335.41	15,889.87	16,250.00	-360.13	-2.22	2025-01-01
16	Stock C	7,041.41	209.26	14,734.85	14,625.00	109.85	0.75	2024-12-12
17	Stock C	7,041.41	209.62	14,760.20	14,625.00	135.20	0.92	2024-12-13
18	Stock C	7,041.41	209.34	14,740.48	14,625.00	115.48	0.79	2024-12-17
19	Stock C	7,041.41	209.15	14,727.10	14,625.00	102.10	0.70	2024-12-18
20	Stock C	7,041.41	207.75	14,628.52	14,625.00	3.52	0.02	2024-12-20
21	Stock C	7,041.41	207.73	14,627.11	14,625.00	2.11	0.01	2024-12-27
22	Stock C	7,041.41	208.25	14,663.73	14,625.00	38.73	0.26	2025-01-01
23	Stock D	3,984.78	264.90	10,555.69	9,950.00	605.69	6.09	2024-12-12
24	Stock D	3,984.78	265.80	10,591.55	9,950.00	641.55	6.45	2024-12-13
25	Stock D	3,984.78	265.60	10,583.58	9,950.00	633.58	6.37	2024-12-17
26	Stock D	3,984.78	264.10	10,523.81	9,950.00	573.81	5.77	2024-12-18
27	Stock D	3,984.78	258.60	10,304.65	9,950.00	354.65	3.56	2024-12-20
28	Stock D	3,984.78	263.70	10,507.87	9,950.00	557.87	5.61	2024-12-27
29	Stock D	3,984.78	261.70	10,428.17	9,854.62	573.55	5.82	2025-01-01
30	Stock E	436.00	1809.20	7,888.11	8,126.30	-238.19	-2.93	2024-12-12
31	Stock E	436.00	1730.60	7,545.42	8,126.30	-580.88	-7.15	2024-12-13
32	Stock E	436.00	1661.40	7,243.70	8,126.30	-882.60	-10.86	2024-12-17
33	Stock E	436.00	1644.20	7,168.71	8,126.30	-957.59	-11.78	2024-12-18
34	Stock E	436.00	1645.20	7,173.07	8,126.30	-953.23	-11.73	2024-12-20
35	Stock E	436.00	1628.80	7,101.57	8,126.30	-1,024.73	-12.61	2024-12-27
36	Stock E	436.00	1633.20	7,120.75	8,126.30	-1,005.55	-12.37	2025-01-01
37	Stock F	231.00	3586.50	8,284.82	8,118.20	166.62	2.05	2024-12-12
38	Stock F	231.00	3630.50	8,386.46	8,118.20	268.26	3.30	2024-12-13
39	Stock F	231.00	3578.50	8,266.34	8,118.20	148.14	1.82	2024-12-17
40	Stock F	231.00	3551.00	8,202.81	8,118.20	84.61	1.04	2024-12-18
41	Stock F	231.00	3525.00	8,142.75	8,118.20	24.55	0.30	2024-12-20
42	Stock F	231.00	3487.50	8,056.13	8,118.20	-62.07	-0.76	2024-12-27
43	Stock F	231.00	3489.50	8,060.75	8,118.20	-57.45	-0.71	2025-01-01
44	Stock G	7,701.42	185.60	14,293.84	14,625.00	-331.16	-2.26	2024-12-12
45	Stock G	7,701.42	185.60	14,293.84	14,625.00	-331.16	-2.26	2024-12-13
46	Stock G	7,701.42	185.20	14,263.03	14,625.00	-361.97	-2.48	2024-12-17
47	Stock G	7,701.42	182.60	14,062.80	14,625.00	-562.20	-3.84	2024-12-18
48	Stock G	7,701.42	180.50	13,901.07	14,625.00	-723.93	-4.95	2024-12-20
49	Stock G	7,701.42	182.80	14,078.20	14,625.00	-546.80	-3.74	2024-12-27
50	Stock G	7,701.42	183.30	14,116.71	14,625.00	-508.29	-3.48	2025-01-01
Sheet1

 

[alansidman]

Here is a sample worksheet I use to track stock gains and losses. I've been using this method for over 20 years. Note that using this system requires a different sheet for each stock. I then summarize using vlookup on a separate page.

Sample Purchase and Sale.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Stocl Performance Analyzer																	Amount
2	Menu!A1																	FALSE
3		******** Transaction ********				******** Tax Ledger ********			************ Market Value ************				********** Return on Investment **********
4	ABC Company													Fund	Annualized
5	ABC		Number	Cost		Number		Average				Market	Outside	Assets +	Return on	Annualized
6			of	Per		of	Tax	Cost		Per		Gain or	Assets	Cash	Initial	Periodic
7	Date	Transaction	Shares	Share	Amount	Shares	Cost	Per Shr	Date	Share	Total	(Loss)	Invested	Withdrawals	Investment	Return
8
9	12/24/2015	Purchase	12,537.598	$15.7100	$ 196,965.67	12537.5980	$196,965.67	$15.710	12/24/15	$15.710	$196,965.67	$0.00	$196,965.67	$196,965.67
10	12/31/2015	Div Reinvest	1.011	$15.7200	$ 15.90	12538.6090	$196,981.57	$15.710	12/31/15	$15.720	$197,106.93	$141.26	$196,965.67	$197,106.93	3.81%	3.81%
11	1/29/2016	Purchase	1,000.000	$15.7800	$ 15,780.00	13538.6090	$212,761.57	$15.715	01/29/16	$15.780	$213,639.25	$893.58	$212,745.67	$213,639.25	4.70%	4.91%
12	3/1/2016	Sale	(500.000)	$15.8100	$ (7,905.00)	13038.6090	$204,903.98	$15.715	03/01/16	$15.810	$206,140.41	($6,605.26)	$212,745.67	$206,140.41	-16.73%	-33.47%
13	4/1/2016	Div Reinvest	4.365	$15.7600	$ 68.80	13042.9740	$204,972.78	$15.715	04/01/16	$15.760	$205,557.27	($7,188.40)	$212,745.67	$205,557.27	-12.81%	-3.28%
Sheet1

Cell Formulas
Range	Formula
R2	R2	=E9<0
D9	D9	=E9/C9
F9	F9	=C9
G9	G9	=E9
H9:H13	H9	=G9/F9
M9,I9:I13	I9	=A9
J9:J13	J9	=D9
K9:K13	K9	=F9*J9
L9:L13	L9	=N9-M9
N9:N13	N9	=K9-DSUM(E$8:E9,(0+1),R$1:R$2)
F10:F13	F10	=IF(CELL("contents",B10:B10)="Dividend",F9,F9+C10)
G10:G13	G10	=IF(CELL("contents",B10:B10)="Dividend",G9,IF(CELL("contents",B10:B10)="Sale",F10*H9,G9+E10))
M10:M13	M10	=IF(CELL("contents",B10:B10)="Purchase",M9+E10,M9)
O10:O13	O10	=((IF(M10>M$9,N10-(M10-M$9),N10)/M$9)^(1/((I10-I$9)/365)))-1
P10:P13	P10	=((IF(M10>M9,N10-(M10-M9),N10)/N9)^(1/((I10-I9)/365)))-1
E11:E12	E11	=C11*D11

 

[stefanaalten]

Many thanks Alan! However, all I really want to do is to create a chart to show how the stocks have performed over time, using the data in the format already available in the download, with minimal manipulation. It's all there as far as I can see - the stock name, the value (or the gain/loss), and the date of th report. I envisage a chart which has dates along the horizontal, £ along the vertical axis and then a filled area to reflect the stock value. Like this image (illustration only - the data does not correspond with my example). However, when I select the data and "insert chart" I can't seem to get anything like the illustration shown below. Any ideas/help very much appreciated!

 

[stefanaalten]

Looks like my brain still has some uses ... I managed to work out a solution and yes, it was simple, once I'd realised it!

Here's what I did ...

1) Created pivot table with As at date in the Rows panel, Stock in the Columns panel, and Sum of Value in the Σ Values panel (there is only ever a single value for a given stock on a given date so that works). That gave me a table like this:

Account Summary (to illustrate question).xlsx
	A	B	C	D
3	Sum of Value (£)	Column Labels
4	Row Labels	Stock A	Stock B	Stock C
5	2024-12-12	4,941.81	16,358.46	14,734.85
6	2024-12-13	4,945.53	16,311.24	14,760.20
7	2024-12-17	4,947.71	16,184.69	14,740.48
8	2024-12-18	4,943.61	16,015.54	14,727.10
9	2024-12-20	4,931.30	15,751.75	14,628.52
10	2024-12-27	4,939.50	15,891.57	14,627.11
11	2025-01-01	4,944.63	15,889.87	14,663.73
Sheet2

2) Clicked the PivotChart icon, selected Stacked Area as chart type and hey presto ...

In the simplified example there are only three stocks and seven "as at" dates but I have done this to my actual data which includes 30 stocks and more dates and it looks very legible - just what I was after with a minimum of fuss, yay!