Help calculating planetary postions

[netojose]

I am searching help to make an excel table that will calculate planetary positions with hourly precision. I need something similar to the printscreen bellow. Input for: start date, number of days, planets, geo or helio. The result first big column: first planet (Sun), second column (Moon), third column pair (Sun and Moon). On each we have the date, the current position of the planet or the pair (in the case of the third column) and how far they moved from the starting date.

What is missing is the precision I need: instead of a line for a day, I need 24 lines for a day, each for an hour. So I need the degrees and the minutes.

Let me know if you can help me.

Thanks,

Jose

 

[mikerickson]

Have you looked at

Approximate Positions of the Planets

 

[Logit]

Computing planetary positions

User defined astronomical functions in Excel

planetary positions calculator

 

[netojose]

I am quite illiterate on these calculations. Thanks!

 

[ebea]

The most detailed version for Excel about Planets, and all kind of calculations, positions and similar, are in the Link below. It needs a bit of knowledge of what's going on in the outer Space!

Download

AstroExcel is a spreadsheet, it doesn’t require any special plug-in’s however you will need to enable macros to use the functions. If you are at all concerned please configure your Exce…

astroexcel.wordpress.com

 

[ebea]

An example on calculations, with positions. Details for Moon and Sun!

astroexcel_v20170611.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3			Position						Time								Twilight	degrees	decimal hours	hh	mm
4				°	'	"	decimal			YYYY	MM	DD	hh	mm	ss		true night ends	11,61114	0,774076206	0	46
5			Latitude	54	7	0	54,11667		UT	2020	8	9	19	20	0		astronomical twilight ends	31,47114	2,098075792	2	06
6			Longitude (west is negative)	11	9	47	11,16306		Julian Day	2459071,3							nautical twilight ends	45,56768	3,037845086	3	02
7				meters					J2000	7526,305556							sunrise	56,07005	3,738003137	3	44
8			Altitude	1					Greenwich Siderial Time	16:35							sunset	283,9702	18,93134935	18	56
9									Local meridian	259,9069391	°						nautical twilight begins	294,3979	19,62652799	19	38
10									Local Siderial Time				17	19,6277565			Astronomical twilight begins	308,3318	20,55545218	20	33
11																	true night begins	327,6039	21,84025992	21	50
12
13		Sun Position	distance (AU)	RA degrees	DEC degrees				Carrington Rotation number	2233
14			1,013705	140,096	15,541				Sun's True Longitude	137,6581773
15									Sun's Apparent Longitude	137,6477162
16		RA HRS	RA minutes	RA Seconds	Dec Degrees	Dec Minutes	Dec Seconds		angular size of sun (arc Sec)	1893		1392000
17		9	20	23,0105	15	32	26,72429
18
19
20
21		Hour	Sun Alt	Moon Alt	sun RA	Sun Dec	moon RA	moon Dec	Calculate the geocentric position of the sun using 'sun' and the topo centric position of the moon using 'tmoon', the use 'altaz' to calculate the altitude, repeat calculation for every hour of the day then plot on a graph
22		0	-3,64376	-16,014	140,1	15,54	27,08491	5,441782
23		1	-10,4294	-8,3431	140,14	15,53	27,60773	5,648067
24		2	-15,7466	0,01561	140,17	15,52	28,09963	5,856136
25		3	-19,1765	8,69499	140,21	15,5	28,55811	6,065255
26		4	-20,3912	17,3411	140,25	15,49	28,98276	6,274522
27		5	-19,2612	25,5642	140,29	15,48	29,37544	6,482923
28		6	-15,9081	32,8834	140,33	15,47	29,74015	6,689402
29		7	-10,6549	38,6851	140,37	15,46	30,08285	6,892939
30		8	-3,91977	42,2618	140,41	15,44	30,41105	7,092638
31		9	3,873502	43,0286	140,45	15,43	30,73334	7,28781
32		10	12,33185	40,8529	140,49	15,42	31,05878	7,478036
33		11	21,08395	36,1512	140,53	15,41	31,39623	7,663217
34		12	29,73999	29,6243	140,57	15,39	31,75375	7,843586
35		13	37,82531	21,9573	140,61	15,38	32,13803	8,019705
36		14	44,6929	13,7047	140,65	15,37	32,55397	8,192417
37		15	49,4698	5,30667	140,69	15,36	33,00436	8,362796
38		16	51,22785	-2,85796	140,73	15,34	33,48976	8,532063
39		17	49,52532	-10,4208	140,77	15,33	34,00854	8,701508	use the RA and DEC positions of the sun calculate the Azimuth using 'altaz' convert this measurement (in degrees) to minutes this is he equation of time
40		18	44,7884	-16,9872	140,81	15,32	34,55702	8,8724
41		19	37,94107	-22,1255	140,85	15,31	35,12975	9,045914	Equation of Time	-297,8	(time to add or subtract from sundial)
42		20	29,85908	-25,4024	140,89	15,3	35,71988	9,223051
43		21	21,19255	-26,477	140,92	15,28	36,31954	9,404575
44		22	12,41773	-25,2192	140,96	15,27	36,92037	9,590967
45		23	3,924524	-21,7649	141	15,26	37,51398	9,782377
46
47
48													Calculate Sun's RA and DEC for each day at mid day (UT) using 'sun' convert to ALT/AZ using 'altaz' , plot the Altitude
49			Sun ALT	Sun Az	minutes	year	month	Day	J2000	Sun RA	Sun Dec
50		01-08-2020	52,95845	195,232	60,93	2020	8	1	7518	132,1378565	17,82014
51		02-08-2020	52,70129	195,192	60,769	2020	8	2	7519	133,1051335	17,56242
52		03-08-2020	52,43904	195,155	60,621	2020	8	3	7520	134,0698857	17,29996
53		04-08-2020	52,17177	195,121	60,485	2020	8	4	7521	135,0321225	17,03282
54		05-08-2020	51,89957	195,091	60,362	2020	8	5	7522	135,9918561	16,76109
55		06-08-2020	51,62252	195,063	60,252	2020	8	6	7523	136,9491019	16,48486
56		07-08-2020	51,34072	195,038	60,154	2020	8	7	7524	137,9038782	16,20421
57		08-08-2020	51,05426	195,017	60,068	2020	8	8	7525	138,8562058	15,91922
58		09-08-2020	50,76321	194,998	59,994	2020	8	9	7526	139,8061087	15,62997
59		10-08-2020	50,46766	194,983	59,932	2020	8	10	7527	140,7536133	15,33656
60		11-08-2020	50,16772	194,97	59,881	2020	8	11	7528	141,6987488	15,03906
61		12-08-2020	49,86346	194,96	59,842	2020	8	12	7529	142,641547	14,73757
62		13-08-2020	49,55498	194,953	59,813	2020	8	13	7530	143,5820422	14,43217
63		14-08-2020	49,24237	194,949	59,796	2020	8	14	7531	144,520271	14,12294
64		15-08-2020	48,92573	194,947	59,789	2020	8	15	7532	145,4562728	13,80998
65		16-08-2020	48,60513	194,948	59,792	2020	8	16	7533	146,3900888	13,49336
66		17-08-2020	48,28068	194,951	59,805	2020	8	17	7534	147,3217628	13,17319
67		18-08-2020	47,95247	194,957	59,827	2020	8	18	7535	148,2513408	12,84953
68		19-08-2020	47,62059	194,965	59,858	2020	8	19	7536	149,1788707	12,52249
69		20-08-2020	47,28514	194,975	59,899	2020	8	20	7537	150,1044027	12,19215		Calculate Sun's RA and DEC for each day at mid day (UT) using 'sun' convert to ALT/AZ using 'altaz' , plot the Azimuth
70		21-08-2020	46,94621	194,987	59,947	2020	8	21	7538	151,0279887	11,8586
71		22-08-2020	46,6039	195,001	60,004	2020	8	22	7539	151,9496829	11,52192
72		23-08-2020	46,2583	195,017	60,069	2020	8	23	7540	152,869541	11,18221
73		24-08-2020	45,9095	195,035	60,141	2020	8	24	7541	153,7876207	10,83955
74		25-08-2020	45,55761	195,055	60,22	2020	8	25	7542	154,7039814	10,49403
75		26-08-2020	45,20271	195,076	60,305	2020	8	26	7543	155,6186841	10,14574
76		27-08-2020	44,84491	195,099	60,397	2020	8	27	7544	156,5317915	9,794774
77		28-08-2020	44,4843	195,124	60,495	2020	8	28	7545	157,4433677	9,441214
78		29-08-2020	44,12099	195,149	60,598	2020	8	29	7546	158,3534784	9,08515
79		30-08-2020	43,75506	195,176	60,706	2020	8	30	7547	159,2621907	8,726673
80		31-08-2020	43,38661	195,205	60,818	2020	8	31	7548	160,1695731	8,365872
81		01-09-2020	43,01576	195,234	60,935	2020	9	1	7549	161,0756952	8,002835
82		02-09-2020	42,64258	195,264	61,056	2020	9	2	7550	161,9806282	7,637654
83		03-09-2020	42,26718	195,295	61,181	2020	9	3	7551	162,8844441	7,270416
84		04-09-2020	41,88967	195,327	61,308	2020	9	4	7552	163,7872162	6,901213
85		05-09-2020	41,51014	195,36	61,438	2020	9	5	7553	164,6890191	6,530133
86		06-09-2020	41,12869	195,393	61,571	2020	9	6	7554	165,589928	6,157268
87		07-09-2020	40,74543	195,426	61,705	2020	9	7	7555	166,4900195	5,782706
88		08-09-2020	40,36045	195,46	61,841	2020	9	8	7556	167,3893707	5,406539
89		09-09-2020	39,97385	195,495	61,978	2020	9	9	7557	168,2880599	5,028857
90		10-09-2020	39,58574	195,529	62,116	2020	9	10	7558	169,1861661	4,649751		Calculate Sun's RA and DEC for each day at mid day (UT) using 'sun' convert to ALT/AZ using 'altaz' , then calculate how many minutes past or to go for the Sun to be at the local meridian
91		11-09-2020	39,19623	195,564	62,255	2020	9	11	7559	170,0837691	4,269312
92		12-09-2020	38,8054	195,598	62,393	2020	9	12	7560	170,9809493	3,88763
93		13-09-2020	38,41337	195,633	62,531	2020	9	13	7561	171,8777879	3,504798
94		14-09-2020	38,02024	195,667	62,669	2020	9	14	7562	172,7743666	3,120906
95		15-09-2020	37,62611	195,701	62,805	2020	9	15	7563	173,6707679	2,736046
96		16-09-2020	37,2311	195,735	62,94	2020	9	16	7564	174,5670746	2,350311
97		17-09-2020	36,83529	195,768	63,073	2020	9	17	7565	175,4633701	1,963793
98		18-09-2020	36,43881	195,801	63,205	2020	9	18	7566	176,3597382	1,576584
99		19-09-2020	36,04175	195,833	63,333	2020	9	19	7567	177,2562629	1,188777
100		20-09-2020	35,64422	195,865	63,46	2020	9	20	7568	178,1530288	0,800466
e.g. Solar

Cell Formulas
Range	Formula
R4	R4	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),1,-18)
S4:S11	S4	=R4/360*24
T4:T11	T4	=INT(R4/360*24)
U4:U11	U4	=(S4-T4)*60
R5	R5	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),1,-12)
R6	R6	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),1,-6)
R7	R7	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),1)
R8	R8	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),0)
R9	R9	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),5,-6)
R10	R10	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),0,-12)
R11	R11	=Sunrise(J7,DegDecimal(D5,E5,F5),DegDecimal(D6,E6,F6),0,-18)
G5:G6	G5	=DegDecimal(D5,E5,F5)
J6	J6	=jday(J5,K5,L5,M5,N5,O5,1)
J7	J7	=Days2000(J5,K5,L5,M5,N5,O5,1)
J8	J8	=INT(gst(J7)/360*24)&":"&FIXED((gst(J7)/360*24-INT(gst(J7)/360*24))*60,0)
J9	J9	=gst(J7)+DegDecimal(D6,E6,F6)
M10	M10	=INT(J9/360*24)
N10	N10	=(J9/360*24-M10)*60
C14	C14	=sun(J7,1)
D14	D14	=sun(J7,3)
E14	E14	=sun(J7,2)
J13	J13	=INT(CarringtonRotation(J7))
J14	J14	= SunLongitude(J7,1)
J15	J15	=SunLongitude(J7,2)
J16	J16	=SunSize($J$7)
L16	L16	=696000*2
B17	B17	=INT(D14/15)
C17	C17	=INT((D14/15-B17)*60)
D17	D17	=(D14/15-B17-C17/60)*3600
E17	E17	=INT(E14)
F17	F17	=INT((E14-E17)*60)
G17	G17	=(E14-E17-F17/60)*3600
C22:C45	C22	=altaz($J$7+($B22/24),$F22,$E22,$D$5+$E$5/60,$D$6+$E$6/60,1)
D22:D45	D22	=altaz($J$7+($B22/24),$H22,$G22,$D$5+$E$5/60,$D$6+$E$6/60,1)
E22:E45	E22	=sun($J$7+($B22/24),3)
F22:F45	F22	=sun($J$7+($B22/24),2)
G22:G45	G22	=tmoon($J$7+($B22/24),$G$5,$G$6,3)
H22:H45	H22	=tmoon($J$7+($B22/24),$G$5,$G$6,2)
J41	J41	=(altaz($J$7+($B34/24),$F34,$E34,$D$5+$E$5/60,$D$6+$E$6/60,2)-180)*4
C50:C100	C50	=altaz(I50,K50,J50,$D$5+$E$5/60,$D$6+$E$6/60,1)
D50:D100	D50	=altaz(I50,K50,J50,$D$5+$E$5/60,$D$6+$E$6/60,2)
E50:E100	E50	=(D50-180)*4
F50:F100	F50	=YEAR(B50)
G50:G100	G50	=MONTH(B50)
H50:H100	H50	=DAY(B50)
I50:I100	I50	=Days2000(F50,G50,H50,12,0,0,1)
J50:J100	J50	=sun(I50,3)
K50:K100	K50	=sun(I50,2)
B51:B100	B51	=B50+1

 

[ebea]

As the example above, can't show Graphs, I upload an image, showing the missing Graph, which are generated in the example.