# Excel trick to get data directly from tempest **URL:** \ **Category:** Tempest Owners\ **Tags:** tempest\ **Created:** [April 4, 2022, 8:42pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118 "2022-04-04T20:42:00Z")\ **Posts on this page:** 20\ **Page:** 5
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [August 28, 2024, 12:21pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/103 "2024-08-28T12:21:19Z")
yes it works. thank you. Now I don’t know google sheets well enough, but wouldn’t it be helpful if it automatically added a column with human readable time stamps? ---
**Author:** ![iladyman](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/iladyman/32/2305_2.png) [@iladyman](https://community.tempest.earth/u/iladyman)\ **Post date:** [August 30, 2024, 5:27am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/104 "2024-08-30T05:27:52Z")
Brilliant. I cant believe your post is not bombarded with love hearts. I just tried it and it works great thank you. A great starting point for quick analysis in distant history or for other reasons. Those who had been asking for something just need to come back to the forum and find your post. I might add a link to it from the google sheets thread… cheers Ian 🙂 ---
**Author:** ![yark](https://avatars.discourse-cdn.com/v4/letter/y/ac8455/32.png) [@yark](https://community.tempest.earth/u/yark)\ **Post date:** [October 17, 2024, 7:32pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/105 "2024-10-17T19:32:06Z")
If I could ask a dumb question. I have everything working if I use the day offset 1 or 0 BUT I am not quite sure how to enter the start and end dates in the url to get the data I need. I tried: [https://swd.weatherflow.com/swd/rest/observations/device/](https://swd.weatherflow.com/swd/rest/observations/device/)\?&time\_start=1729192564&time\_end=1729192564&format=csv&token=\ but it only gives a row of headers… I deploy weather stations in locations for 4-6 weeks and need to summarize the WXT data with other data for set time periods. Cheers! ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [October 17, 2024, 8:42pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/106 "2024-10-17T20:42:19Z")
your start time is the same as the end time As the time is in seconds, that would give you no data, or perhaps if you are lucky a single line of data. ---
**Author:** ![yark](https://avatars.discourse-cdn.com/v4/letter/y/ac8455/32.png) [@yark](https://community.tempest.earth/u/yark)\ **Post date:** [October 17, 2024, 9:42pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/107 "2024-10-17T21:42:50Z")
Ahh, I should have caught that…But is the syntax correct? I guess that is the root of my question. I updated the start and end times and it is still giving me just one row. I just Googled to convert the date to Unix [https://swd.weatherflow.com/swd/rest/observations/device/](https://swd.weatherflow.com/swd/rest/observations/device/)\?time\_start=1725532200&time\_end=1729074600&format=csv&token=\ If I do ‘day \_offset=0’ it works just fine. ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [October 18, 2024, 9:29am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/108 "2024-10-18T09:29:32Z")
Yes that syntax seems correct. Perhaps first give it a try on the api page (link in the top of this page). Authorize with your token and request the observations. At least you get some error message if something is wrong ---
**Author:** ![rkalina](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@rkalina](https://community.tempest.earth/u/rkalina)\ **Post date:** [October 28, 2024, 3:50pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/109 "2024-10-28T15:50:59Z")
Hi, I’m very new to tempest weather data and thank you for this technique to get the data from the device as this method works just fine and I’m able to refresh the excel and able to get most current values in new rows too. 1. But I have a question how do you change the interval from 1 minute to a different interval like may be 5 minutes or so? The 60 seconds is a lot of data. 2. Also, if you want to fetch data from a start date you can use time\_start=, but what if you want to get data continuously without end date? ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [October 28, 2024, 4:13pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/110 "2024-10-28T16:13:22Z")
1. if I remember correctly if you use a start and end date seperated by more than a couple of days, it will automatically return data with bigger intervals. 2. there is no unlimited end date. you can however request real time data using the API link in the top of this page. Hope that helps ---
**Author:** ![rkalina](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@rkalina](https://community.tempest.earth/u/rkalina)\ **Post date:** [October 29, 2024, 4:01pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/111 "2024-10-29T16:01:24Z")
Hi. Thats very useful information can I ask you one more doubt. I have this situation to get weather data from device from 2 weeks back till now and moving forward with 1 minute interval in excel. What would be the ideal scenario to do that. It will be very helpful if I could get an opinion on that. Thanks in advance. ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [October 29, 2024, 4:28pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/112 "2024-10-29T16:28:19Z")
depends on what you are hoping to achieve. Getting data from 2 weeks back is easy with the method mentioned above. Do you want “real time” data to make graphs? You can always use the method again whenever you want to make a graph. If you want a method for showing real time data as graphs that automatically stay up to data, there are probably better methods, like the stand alone programs that run on a raspberry pi with attached screen. Those methods use the api to get the data. search for pi console in this forum. ---
**Author:** ![rkalina](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@rkalina](https://community.tempest.earth/u/rkalina)\ **Post date:** [October 29, 2024, 4:39pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/113 "2024-10-29T16:39:35Z")
With the method mentioned above using the [https://swd.weatherflow.com/swd/rest/observations/device/](https://swd.weatherflow.com/swd/rest/observations/device/)?day\_offset=0&format=csv&token= I’m only getting data for one day (1 minute interval). When i press refresh next day the previous data is cleared and new data of new day is updated in same rows. Now when i use start and end date to fetch previous data then the interval changes. My request is simple, I want to get data from last two weeks with 1 minute interval in excel now and if I open excel again tomorrow i need the new data in new rows (with keeping the previous data) with pressing refresh or any data fetching method. I know there is an applet for doing the same exact thing for google sheet but i was hoping to find something for excel. ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [October 29, 2024, 5:10pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/114 "2024-10-29T17:10:42Z")
for the older data, just request data with for example a day\_offset of 14 instead of 0. That should give you 1 minute data from 14 days ago. I don’t know excel well enough, but I would load the latest data into a new sheet and append that data to the existing data (and perhaps check for duplicate timestamps and delete that data) ---
**Author:** ![mabeatty1978](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/mabeatty1978/32/15139_2.png) [@mabeatty1978](https://community.tempest.earth/u/mabeatty1978)\ **Post date:** [October 30, 2024, 3:33pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/115 "2024-10-30T15:33:55Z")
> [@rkalina](#): > > But I have a question how do you change the interval from 1 minute to a different interval like may be 5 minutes or so? The 60 seconds is a lot of data. There is an unpublished “bucket” parameter that accepts a single character, a,b,c,d,e where a = 1 min, b = 5 min, c = 30 min, d = 180min, e =1 day. You can pass that "bucket= " parameter into the api call and it will return results with that interval. It’s worth adding that the reason it is unpublished is that if your provide a date range and a bucket interval that is too short, the call will fail because of there being too much data to return. > [@rkalina](#): > > Also, if you want to fetch data from a start date you can use time\_start=, but what if you want to get data continuously without end date? I’m not entirely sure what you are asking for, but if you want to get data all the way up to the most recent records, you could enter the value` =NOW()` in the end date. ---
**Author:** ![ronau727](https://avatars.discourse-cdn.com/v4/letter/r/278dde/32.png) [@ronau727](https://community.tempest.earth/u/ronau727)\ **Post date:** [November 4, 2024, 8:48pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/116 "2024-11-04T20:48:09Z")
Hello Sunny, Can you help me out how to get the solar radiation W/m2 in excel sheet. Not all the data from my tempest weather station are presented in Excel. So how can I eliminate certain data and how can I add data to the Excel sheet. Appreciate your help. ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [November 4, 2024, 9:38pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/117 "2024-11-04T21:38:10Z")
I don’t know how you manage not to get the solar irradiation. Try this example: > [@karss](#): > > [https://swd.weatherflow.com/swd/rest/observations/device/298688?day\_offset=0&format=csv&token=770e7711-b879-4a8a-aa3d-bc655c7227c7](https://swd.weatherflow.com/swd/rest/observations/device/298688?day_offset=0&format=csv&token=770e7711-b879-4a8a-aa3d-bc655c7227c7) How is your different? ---
**Author:** ![ronau727](https://avatars.discourse-cdn.com/v4/letter/r/278dde/32.png) [@ronau727](https://community.tempest.earth/u/ronau727)\ **Post date:** [November 5, 2024, 12:04am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/118 "2024-11-05T00:04:25Z")
Thank you for your link, but that did not solve it. In Excel after the humidity column all the following columns lux,uv, solar\_radiation, precip,precip\_type, strike distance etc are all zero’s. So not all data is present in the Excel sheet from my tempest weather station. I really need the solar-radiation columns. Any help much appreciated. ---
**Author:** ![ronau727](https://avatars.discourse-cdn.com/v4/letter/r/278dde/32.png) [@ronau727](https://community.tempest.earth/u/ronau727)\ **Post date:** [November 5, 2024, 12:28am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/119 "2024-11-05T00:28:12Z")
![image](https://us1.discourse-cdn.com/flex020/uploads/sws/original/3X/f/e/fe3ae8e8c7b04a948e68eb2831c41dc9b399616c.png) ---
**Author:** ![sunny](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/sunny/32/16368_2.png) [@sunny](https://community.tempest.earth/u/sunny)\ **Post date:** [November 5, 2024, 10:18am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/120 "2024-11-05T10:18:24Z")
ok, at least you have the columns with data. it just appears zero. assuming your sensor is working, scroll down 500 lines or so. The data starts at midnight so their isn’t any solar irradiation ---
**Author:** ![jon5456](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@jon5456](https://community.tempest.earth/u/jon5456)\ **Post date:** [January 5, 2025, 4:21pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/121 "2025-01-05T16:21:47Z")
This is really cool! Thanks for all the tips! I got mine to work right away. ---
**Author:** ![colchicine](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/colchicine/32/17331_2.png) [@colchicine](https://community.tempest.earth/u/colchicine)\ **Post date:** [January 14, 2025, 4:55pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/122 "2025-01-14T16:55:10Z")
Thank you for putting this Google Sheets together! It was the only method I got to work. I’m aware of how to convert the Unix timestamp to an Excel date, but how do I do that with this spreadsheet? It appears I can’t modify the fields as is. Do I have to copy the data to another tab and convert it there? [Previous page](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118.md?page=4) [Next page](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118.md?page=6)