# 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:** 3
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 8, 2023, 10:12am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/57 "2023-09-08T10:12:26Z")
Just saw this thread and found it useful. Is there a way to adjust the data download to local time instead of GMT. For example, if your station is at GMT+2.00 hrs, then the data is downloaded from 2.00 Hrs until 2.00 Hrs next day. It is not 00:00 Hrs to 24:00 Hrs, your local time. Would be nice to download data directly from midnight to midnight for your local time. ---
**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:** [September 8, 2023, 11:07am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/58 "2023-09-08T11:07:37Z")
you can, but in that case you need to specify the start time and and time. They are defined in terms of unix-time (time in seconds since 1 jan 1970) ---
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 8, 2023, 11:19am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/59 "2023-09-08T11:19:04Z")
My computer knowledge is not that good, could you please let me know how to do that. My time zone is GMT + 5.30 Hrs. It would be nice if you can put it in this format: [https://swd.weatherflow.com/swd/rest/observations/device/168731?day\_offset=0&format=csv&token=bcf4754a-886b-4b84-9ce7-5b70c22e5299](https://swd.weatherflow.com/swd/rest/observations/device/168731?day_offset=0&format=csv&token=bcf4754a-886b-4b84-9ce7-5b70c22e5299) ---
**Author:** ![eric](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/eric/32/13553_2.png) [@eric](https://community.tempest.earth/u/eric)\ **Post date:** [September 8, 2023, 11:26am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/60 "2023-09-08T11:26:05Z")
this is the formula, adjust the 7200 to your zone (seconds to add) > [@Excel trick to get data directly from tempest](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/39): > > so the formula to convert unix time to date/time in excel is =(($D2+7200)/86400)+DATE(1970,1,1) and in order to display the date and time, format the column to have a custom format “dd/mm/jjjj uu:mm” Note I added 7200 seconds in order to compensate for the right time zone. You might want to add/subtract according to your time zone. ---
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 8, 2023, 11:32am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/61 "2023-09-08T11:32:40Z")
I think you got my question wrong, I already saw your post and was able to adjust the data in the excel table after downloading the CSV file. That is when I found out that your https script is pulling data from 5.30 am to 5.30 am next day for my station. Instead I want data to be downloaded from midnight to midnight for my time zone. Is this possible? Also, I found that simply pasting the https script into the web browser downloads the csv file! Which can then be opened using Excel. Further, I had to change the custom date format to dd-mm-yyyy hh:mm in my case. ---
**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:** [September 8, 2023, 11:38am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/62 "2023-09-08T11:38:58Z")
in excel you can do =Date(2023,9,1) - Date(1970,1,1) to get the number of days in between. Multiply that by 24 \* 60 \* 60 to get the number of seconds. add (or substract, i never know) 5.5 \* 3600 to compensate for your timezone. that would be your end time in seconds. Subtract 24 \* 60 \* 60 to get the start time. you might end up with a call like this [https://swd.weatherflow.com/swd/rest/observations/device/168731?time\_start=1693240000&time\_end=1693506400&format=csv&token=bcf4754a-886b-4b84-9ce7-5b70c22e5299](https://swd.weatherflow.com/swd/rest/observations/device/168731?time_start=1693240000&time_end=1693506400&format=csv&token=bcf4754a-886b-4b84-9ce7-5b70c22e5299) (but with different numbers for time\_start and time\_end) ---
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 8, 2023, 12:12pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/63 "2023-09-08T12:12:13Z")
Thanks for the reply. Will give it a try. ---
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 8, 2023, 1:04pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/64 "2023-09-08T13:04:12Z")
Hi Sunny, thanks, it works. Just a quick question, is there a way to auto download the CSV file every day, instead of a manual download (a crontab function in RPi or a google script)?. This way it will be helpful to have a personal backup of the tempest data. Unfortunately, as you have mentioned earlier, if I increase the data duration from one day to one month, the data resolution becomes drops from one minute interval to three hour interval. We also cannot see old data with higher resolution on the tempest website. An auto download function of the CSV file will be very useful. ---
**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:** [September 8, 2023, 1:52pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/65 "2023-09-08T13:52:56Z")
sorry, can’t help you with that automation. You can however download 5 minute data using the methods mentioned, using day-offset or start/end-time. ---
**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:** [September 9, 2023, 1:49am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/66 "2023-09-09T01:49:19Z")
> [@karss](#): > > is there a way to auto download the CSV file every day, instead of a manual download (a crontab function in RPi or a google script)?. This way it will be helpful to have a personal backup of the tempest data. Yes that would be possible. Only my opinion though is that it is not helpful. You would have those files in your memory and would then need to manipulate them. Those files are always available from Weatherflow unless you delete your Tempest from their system using your App. They are available to you using that link in one minute resolution in 24 hour blocks in their database going back in time from the moment you installed your Tempest. If you are going to use a RPi then you could install weewx and have the data on your computer and able to view its history in a presentable way. cheers Ian 🙂 ---
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 9, 2023, 2:53pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/67 "2023-09-09T14:53:35Z")
Is there a way to download the data directly from Weatherflow, other than the Excel and Google Sheet options mentioned above? With the above two options you have to do mathematical calculation to arrive at the start time and end time to get the data for a particular day in the past, which is really cumbersome. ---
**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:** [September 9, 2023, 3:11pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/68 "2023-09-09T15:11:09Z")
the only way is through their API (you can click on the API in the top of this window), but if you want a particular day, you have to tell it which day that is. In offsets from the current day or by telling it the start and end times. How else would you do that? Start and end times go in unix-time, because that is what computers use. ---
**Author:** ![eric](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/eric/32/13553_2.png) [@eric](https://community.tempest.earth/u/eric)\ **Post date:** [September 10, 2023, 7:54am UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/69 "2023-09-10T07:54:22Z")
From top of the head, [Weather-Display](https://www.weather-display.com) can I think. You can ask on [their forum](https://discourse.weather-watch.com) for details. ---
**Author:** ![karss](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tempest.earth/karss/32/16530_2.png) [@karss](https://community.tempest.earth/u/karss)\ **Post date:** [September 10, 2023, 12:11pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/70 "2023-09-10T12:11:08Z")
I tried to link my Tempest to Weather Underground using Weather Display installed on a Raspberry Pi 4B, though it is connected and uploaded data to Weather Underground, there is problem with wind speed, wind direction, rain, UV and solar radiation data. Wind direction seems to be stuck in one direction, wind speed and gust oscillates between just three values. Rain, UV and Solar radiation data are not reported. So I gave up on Weather Display! ---
**Author:** ![insixdays41705](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@insixdays41705](https://community.tempest.earth/u/insixdays41705)\ **Post date:** [October 2, 2023, 12:20pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/71 "2023-10-02T12:20:48Z")
I get the same. I believe I am using the Devide ID but not 100% sure. On the Web app, I go to Settings, Manage Stations, select my station, then manage Devices. I see an icon for the Tempest device with “ST-00126272”. I used the number part of this (i.e. 00126272) in the URL but get the credentials screen exactly as shown in the screenshots. ---
**Author:** ![insixdays41705](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@insixdays41705](https://community.tempest.earth/u/insixdays41705)\ **Post date:** [October 2, 2023, 12:28pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/72 "2023-10-02T12:28:18Z")
> [@sunny](#): > > [https://swd.weatherflow.com/swd/rest/observations/device/](https://swd.weatherflow.com/swd/rest/observations/device/)?day\_offset=0&format=csv&token= Update to my previous reply. I tried using the number given in the “Share Page” link (119813) but get the same error. Here is the URL I’m using: [https://swd.weatherflow.com/swd/rest/observations/device/119813?day\_offset=0&format=csv&token=001262726e4cae5c-7650-4be7-8cfc-8c40fa9f4f80](https://swd.weatherflow.com/swd/rest/observations/device/119813?day_offset=0&format=csv&token=001262726e4cae5c-7650-4be7-8cfc-8c40fa9f4f80) The token part was very straightforward, I followed the steps as given and clicked the “Copy token” button and then pasted into the URL. I don’t see how that could be wrong. ---
**Author:** ![insixdays41705](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@insixdays41705](https://community.tempest.earth/u/insixdays41705)\ **Post date:** [October 2, 2023, 1:43pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/74 "2023-10-02T13:43:01Z")
Hello. Now I get a 404:Not Found error ![image](https://us1.discourse-cdn.com/flex020/uploads/sws/original/2X/9/993eb199e794408b911f46b9825dcf8020fce462.png) ---
**Author:** ![insixdays41705](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@insixdays41705](https://community.tempest.earth/u/insixdays41705)\ **Post date:** [October 2, 2023, 1:46pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/75 "2023-10-02T13:46:45Z")
Looking back at previous postings, it appears the station ID versus device ID is the issue. I just got my Tempest last week and am totally new at this. I’m really not sure how to find my devide ID. Can you help with that part? ---
**Author:** ![insixdays41705](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@insixdays41705](https://community.tempest.earth/u/insixdays41705)\ **Post date:** [October 2, 2023, 2:39pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/77 "2023-10-02T14:39:21Z")
sunny, thank you so much for your help. When you say, “Are you using the same username account the tempest is using” what do you mean exactly? I am logged into the Tempest web app with my Tempest username and password, otherwise, I could not get into the settings. When launching the Excel getdata, it doesn’t ask me for login information unless I select one of the other options when I get the unauthorized message but I don’t get that now. The rest of your instructions are quite complicated but I’m going to try them shortly. ---
**Author:** ![insixdays41705](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@insixdays41705](https://community.tempest.earth/u/insixdays41705)\ **Post date:** [October 2, 2023, 2:43pm UTC](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118/78 "2023-10-02T14:43:57Z")
sunny, executed your instructions regarding authorize. The message body says “success”. [Previous page](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118.md?page=2) [Next page](https://community.tempest.earth/t/excel-trick-to-get-data-directly-from-tempest/17118.md?page=4)