Could someone please teach me how to write VBA code to download historical data from web

You don't have to write VBA. If you have Office 365, this is now a built-in feature. Please look up the STOCKHISTORY function.

Quick example:

Values as displayed

	A	B	C
1	GOOGL	Date	Close
2		1/2/2020	$ 1,368.68
3		1/3/2020	$ 1,361.52
4		1/6/2020	$ 1,397.81
5		1/7/2020	$ 1,395.11
6		1/8/2020	$ 1,405.04
7		1/9/2020	$ 1,419.79
8		1/10/2020	$ 1,428.96
9		1/13/2020	$ 1,440.03
10		1/14/2020	$ 1,430.59
11		1/15/2020	$ 1,439.20

Underlying formulas

	A	B	C
1	GOOGL	=STOCKHISTORY(A1,"1/1/2020",TODAY())
2
3
4
5
6
7
8
9
10
11

Originally Posted by 6StringJazzer

You don't have to write VBA. If you have Office 365, this is now a built-in feature. Please look up the STOCKHISTORY function.

Good to know, but I think I do need VBA code. I don't think I want to do it one sheet at a time. As first post states, I would like to pull historical data for a list of S&P 500 companies, not just a few companies.

Edit: I think I can write VBA code from excel function =STOCKHISTORY("MSFT",TODAY()-30,TODAY())