Hi Donald, 1. What brand of spreadsheet are you using? Excel? Can you deal with Excel Visual Basic functions? 2. You will also need to know time zone and whether day-light savings is in place.
Below are some Visual Basic functions that your will need. My algorithms aren't precise because I used them for solar radiation calculations, so I don't include daylight savings adjustments. This will give you the Sun's Altitude Angle, You will also need the Sun's Azimuth which I didn't need. Here gmtdate is an excel date. GMToffset is the offset of your timezone (+daylight savings) in hours (east +, west -). Latitude is positive north, and longitude positive east. I could dig up the azimuth formula if you need. Google says that Autodesk Inventor can be programmed in Visual Basic, so these functions should work directly into Inventor, thus spreadsheet shouldn't be necessary. Hope this helps Hank, Adelaide, Australia Const pi = 3.1415926535 Function AltitudeAngle(ByVal lat, ByVal gmtdate, ByVal GMToffset, ByVal lon) dec = declination(gmtdate) solar_t = solartime(gmtdate, GMToffset, lon) Ha = HourAngle(Hour(solar_t) + Minute(solar_t) / 60) sinalt = Sin(pi / 180 * lat) * Sin(pi / 180 * dec) + Cos(pi / 180 * lat) * Cos(pi / 180 * dec) * Cos(Ha) alt = 180 / pi * Arcsin(sinalt) If alt < 0 Then alt = -1 AltitudeAngle = alt End Function Function HourAngle(ByVal solarhour) HourAngle = pi / 12 * (solarhour - 12) End Function Function Sunrise(ByVal gmtdate, ByVal GMToffset, ByVal latitude, ByVal longitude) Dim dec, cosha dec = declination(gmtdate) cosha = -Tan(pi / 180 * dec) * Tan(pi / 180 * latitude) Ha = 12 / pi * Arccos(cosha) Sunrise = solar2standard(12 - Ha, gmtdate, GMToffset, longitude) / 24 End Function Function Sunset(ByVal gmtdate, ByVal GMToffset, ByVal latitude, ByVal longitude) Dim dec, cosha dec = declination(gmtdate) cosha = -Tan(pi / 180 * dec) * Tan(pi / 180 * latitude) Ha = 12 / pi * Arccos(cosha) Sunset = solar2standard(12 + Ha, gmtdate, GMToffset, longitude) / 24 End Function Function Arcsin(ByVal x) Arcsin = Atn(x / Sqr(-x * x + 1)) End Function Function Arccos(ByVal x) Arccos = Atn(-x / Sqr(-x * x + 1)) + 2 * Atn(1) End Function ' Calculate the local solar time of a given GMT time Function solartime(ByVal gmtdate, ByVal GMToffset, ByVal longitude) standardTime = gmtdate + GMToffset / 24 standardHour = (standardTime - Int(standardTime)) * 24 solartime = Int(standardTime) + (standardHour + (longitude / 15 - GMToffset) + EquationOfTime(gmtdate) / 60) / 24 End Function ' Calculate the Standard hour of a given solar hour Function solar2standard(ByVal solhour, ByVal gmtdate, ByVal GMToffset, ByVal longitude) solar2standard = solhour - ((longitude / 15 - GMToffset) + EquationOfTime(gmtdate) / 60) End Function Function dayofyear(ByVal gmtdate) Dim d, m, y Rem Calculate the day of the year for a non leap year date Rem GMT should be provided as it is required for the Declination, Equation of Time, and Radius Factor d = Day(gmtdate) m = Month(gmtdate) y = 2002: Rem use a non leap-year dayofyear = DateSerial(y, m, d) - DateSerial(y, 1, 1) + 1 + (gmtdate - Int(gmtdate)) End Function Function declination(ByVal gmtdate) Dim n, t n = dayofyear(gmtdate) t = 2 * pi * (n - 1) / 365.25 declination = 0.37835994 + 23.264303 * Cos(t + 3.324439) + 0.38088907 * Cos(2 * t + 3.271604) + 0.17119315 * Cos(3 * t + 3.67194) End Function Rem Equation of Time in minutes Rem Regression Equation derived by Hank de Wit from Meeus' astronomical formula Function EquationOfTime(ByVal gmtdate) Dim n, t n = dayofyear(gmtdate) t = 2 * pi * (n - 1) / 365.25 EquationOfTime = -0.000062368659 + 7.3636768 * Cos(t + 1.5076785) + 9.9351605 * Cos(2 * t + 1.9332853) + 0.32924369 * Cos(3 * t + 1.8934472) + 0.21208209 * Cos(4 * t + 2.3032843) End Function ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Donald Christensen Sent: Wednesday, 24 August 2011 12:00 To: [email protected] Subject: sun position I need to write a spreadsheet to find the sun position. I know there are already many programs that do this. However, I need to write one myself because: 1. I'll learn as I write 2. It will give the sun position in the format that I need 3. I can tweak the spreadsheet to give the same answer but in another format. My question is, where do I start? I want to input Latitude Longitude Time The sun position will then be in X Y Z coordinates. I'll back up. I'm drawing a sundial in Autodesk Inventor. I can place lights that represent the sun. I can move the lights while I film my animation and watch the shadows move. That's why I need the sun position in XYZ format. I want the shadows accurate. I don't need the sun to be 150,000,000 km away from my sundial. It only needs to be 100 meters away I want to first write it to output azmith and altitude. (or the same as other programs whatever the output is) I'll then tweak my spreadsheet to convert this to XYZ -- Cheers Donald 0423 102 090 This e-mail is privileged and confidential. If you are not the intended recipient please delete the message and notify the sender. Un-authorized use of this email is subject to penalty of law. So there!
--------------------------------------------------- https://lists.uni-koeln.de/mailman/listinfo/sundial
