STOCKHISTORY showing price change 30 days back for 20 stocks

[AllanSE]

Hi all

I'm new here since I currently use Google Sheets to track my portfolio but Google Finance is getting increasingly unreliable, so I would like to migrate to Excel and O365. I already have O365 subscription.

I would like to see the following.
In my example sheet, I have:
Column A stocks names
Column B should show price change 30 days back in % but it fails (formula: =STOCKHISTORY(A2;TODAY()TODAY()-30;0;1;1)
Columns C:H some stats, it works but I simply can't get it to continue downwards

Can you see where I do mistakes? Hope I have followed all the forum rules. Here is an editable link to where I try out the different functions. Sheet is called MrExcel.

Thanks in advance.
Allan, Sweden

 

[Georgiboy]

You have your start and end dates the wrong way around, try as below:

=STOCKHISTORY(A2;TODAY()-30;TODAY();0;1;1)

 

[Georgiboy]

I have edited that file, when using the STCKHISTORY function in that way it is best to turn off the headers part in the formula. See if the edits made help?

 

[AllanSE]

Hi Georg!
I see you heavily are working on the sheet! Big thanks for your help, time and support. If I don't answer it's because I need to take my daughter to the doctor but hope to be back soon.

Thanks.
Allan, Sweden

 

[Georgiboy]

I have come out of the file now, worth noting that the TODAY()-30 does not always return the amount of results expected, i noted some stocks returned 18 results and others 21+, might be somthing to investigate (unless it excludes weekends and bank holidays)

Below is how it ended up:

STOCKHISTORY.xlsx
	A	B	C	D	E	F	G	H
2	Swedbank AB (OTCM:SWDBY)	-2.40%	01/06/2022	2,899,852	$ 15.24	$ 15.90	$ 12.50	$ 12.63
3	Tele2 AB (XSTO:TEL2 B)	-1.60%	01/06/2022	48,726,837	120.40 kr	122.10 kr	111.70 kr	116.50 kr
4	Thule Group AB (XSTO:THULE)	-10.77%	01/06/2022	8,147,107	329.10 kr	330.50 kr	245.10 kr	251.30 kr
5	Volvo AB (XSTO:VOLV A)	-4.69%	01/06/2022	2,678,957	183.40 kr	190.20 kr	162.10 kr	164.60 kr
6	3M COMPANY (XNYS:MMM)	0.44%	01/06/2022	63,798,266	$ 149.33	$ 149.49	$ 127.30	$ 129.41
7	ABBVIE INC. (XNYS:ABBV)	8.36%	01/06/2022	132,594,299	$ 147.79	$ 156.62	$ 135.76	$ 153.16
8	Acea SpA (XMIL:ACE)	-4.40%	01/06/2022	3,449,877	€ 16.90	€ 17.18	€ 13.43	€ 14.11
9	Acadian Timber Corp. (XTSE:ADN)	-3.30%	01/06/2022	178,205	$ 18.33	$ 18.55	$ 16.01	$ 16.66
10	Acerinox SA (BMEX:ACX)	-4.95%	01/06/2022	36,431,467	€ 12.16	€ 12.41	€ 9.15	€ 9.24
11	AGNC INVESTMENT CORP. (XNAS:AGNC)	10.28%	01/06/2022	284,248,593	$ 12.25	$ 12.41	$ 9.99	$ 11.07
12	ALARIS EQUITY PARTNERS INCOME TRUST (XTSE:AD.DB.A)		01/06/2022	1,114,000	$ 99.00	$ 99.00	$ 93.00	$ 96.85
MrExcel

Cell Formulas
Range	Formula
B2:B12	B2	=IFERROR(LET(s,STOCKHISTORY(A2,TODAY()-30,TODAY()-1,0,0,1),st,TAKE(s,1),en,TAKE(s,-1),(st-en)/st),"")
C2:H12	C2	=STOCKHISTORY(A2,EOMONTH(TODAY(),-11),,2,0,0,5,2,3,4,1)
Dynamic array formulas.

 

[AllanSE]

Hello again Georg

So many thanks - this and your support will be a big step for me to migrate to Excel. I'll of course keep the file shared if anyone else could benefit from the solution.

Thanks again - wish you a great weekend.
Allan, Sweden