Import Data From Tempest To Excel

Tempest Owners
1

Here is how I imported data into an Excel Spreadheet on a PC

In Excel, open a new blank spreadsheet

Data>Get Data>From Other sources>From Web

I wanted the time interval to be 300 seconds or 5 minutes for my purposes. I used the following web address string to get 5 days of data:

Note: At some point, the time interval increases to 30 minutes, so I stayed with 5 days. A single day’s worth of data using the day_offset method yields a one minute time interval & that was too much detail for my needs.

In the URL Box, I entered the following (except use your own device ID, start/end times & Token).

h t t p s://swd.weatherflow.com/swd/rest/observations/device/yourdeviceID?time_start=1717995600&time_end=1718427600&format=csv&token=yourtoken

Note : remove spaces from the https

Click OK

in the Data Type Detection pulldown, select “Do not detect data types”

at bottom right, select Load>Load To…

select Table & New Worksheet

The Unix timestamps in the above example represent a starting time of June 10, 2024 0:00 & span exactly 5 days to midnight at the end of the 14th.

on a temporary worksheet…
format the date column as you like. I selected Date Type 3/14/12 13:30
format the UNIX timestamp column as a number
for a date, I entered 6/10/24 0:00

I used the following excel formula to convert the dates/times I wanted to a UNIX timestamp :

=(A4-DATE(1970,1,1)) * 86400+18000

the 18000 converts UTC to my Time Zone. 18000 seconds represents the 5 hour adjustment for my zone. Also, change the A4 to your ‘date cell’ on the spreadsheet.

on the Tempest data spreadsheet, you can convert the UNIX timestamps, after formatting the column(s) using :

=(your timestamp cell + or - your time zone shift)/86400+(DATE(1970,1,1))

example : =(C11-18000)/86400+(DATE(1970,1,1))

Converting station pressure in mb to sea level in inHg was trickier but same as Tempest equation. I used the excel formula :

=your station pressure in mb * (1+(1013.25/your station pressure in mb)^0.19 * (0.0065 * your station elevation in m/288.15))^5.256 + or - any adjustment) * .02952998

I used a -.4 adjustment to be in agreement with what my device is displaying. I’ll keep an eye on it. I don’t understand why the formula doesn’t exactly match what Tempest is doing (it’s their own equation).

I deleted columns 1, 2, 3, 13, 14, 15, 20, 21 & 26
I added columns 4a, 5a, 6a, 7a, 10a, 11a, 16a, 22a, 23a & 24a to do the conversions.

1 Like
2

have you forgotten to add the height of the sensor above the ground?

3

It’s in there. To simplify the formula, I summed the two heights. Thanks for taking a look.

=your station pressure in mb * (1+(1013.25/your station pressure in mb)^0.19 * (0.0065 * your station elevation in m/288.15))^5.256 + or - any adjustment) * .02952998

The station pressure gets used in two places in the equation.

The .02952998 changes from mb to inHg.

For height above ground in this equation, add the site’s elevation above sea level to the device height above ground. Tempest’s equation also sums both.

The calculation to convert station pressure to sea level pressure has many methods. Some include humidity, air density & all sorts of complications & I’m certain they all play a role to various degrees. I wanted to use the equation that Tempest uses, but it seems to overshoot a small amount for my location. For now, I’ll simply use a negative 0.4 adjustment, until I figure it out. It was curious that Tempest uses a positive value for standard atmospheric lapse rate while most everyone else use a negative value. Their formula however, only produces good results using a positive value of .0065.

Looking at the Excel data only raised many more questions for me. I’ll scratch my head for a while before I ask for help. It was cool that Tempest recognized hail. I’ll need to pay close attention to that next time we get some & take notes about the times.

Thanks sunny

4

in tempest terms station elevation is at ground level. So it will be confusing. Just write it as (station_elevation + height_above_ground)
((did you set both values correctly in the tempest settings?)) Can you give an numerical example of the formula overshooting?

Wikipedia reports a positive value of 6.50 °C/km for the standard atmospheric lapse rate (the rate at which temperature decrease with height) Lapse rate - Wikipedia)

Tempest has a value defined for hail, but I don’t think it will report as such.

5

I wouldn’t have believed it but a 3m difference between the accurate elevation & height provided to Tempest for my station & the number I used in the equation caused the 0.4 mb difference to get sea level from station pressure. I’ve learned to not question Tempest’s tech staff or you guys with experience! Rounding up from 747 to 750 is what caused all this hubbub. As an engineer, I should of known better. Dang, sorry about all the fuss!

Thank you for checking the sign of the lapse rate. I think what happens is that some equations other sources use require it to be negative. My bad for even mentioning that. Tempest uses an average value & puts it in meters rather than km to coincide with other parameters in the equation that all use meters.

There is a column in the Excel file exported from Tempest that defines the precipitation type, Column17. 0 is none, 1 is rain, 2 is hail & 3 is a rain/hail mix. My report listed many occurrences as type 2 precipitation, or hail. I don’t know if Tempest modifies their accumulation numbers or not if it detects hail. I’ll be watching more closely now that I know the device can recognize the difference. We’ve been getting a lot of hail this month.

Thanks @sunny

1 Like