Introduction: How to Retrieve/Export Data From a DeepBlu Cosmiq+ Dive Computer
DeepBlu Cosmiq+ dive computers are very user-friendly dive computers, but they do not offer a way to easily export your data. They do offer integration with third-party dive logging applications such as Subsurface (https://subsurface-divelog.org/), but that is beyond the scope of this article.
Retrieving your data from DeepBlu is possible thanks to their website, which uses an API that you can also manipulate to retrieve your raw data. All you need is a laptop or desktop computer with a web browser (it's probably possible on a tablet or mobile device, but it will be much easier to do with a computer).
Once you have the data, it's up to you to decide how you want to use it. This instructable will provide instructions for how to take the output data and store it in an Excel file.
Note: This tutorial assumes you have set up a DeepBlu account and have synchronized dive logs with the DeepBlu app. If you have not set up an account with DeepBlu, you will need to create one first.
Supplies
- Laptop or Desktop Computer (needed to retrieve the data from DeepBlu)
- Microsoft Excel (needed to view and/or convert the data). One of the following versions are required to use Power Query Editor:
- Excel for Microsoft 365
- Excel 2021
- Excel 2019
- Excel 2016
- Excel 2013
- Excel 2010
Step 1: Log in to DeepBlu
On your computer, open a web browser (Google Chrome, Microsoft Edge, Apple Safari, Mozilla Firefox, etc.) and go to the website https://www.deepblu.com.
Once there, find and click the "Log In" button.
Once the site prompts for your username and password, input the information and click the "Login."
Step 2: Find Your DeepBlu User ID
Once you have logged in, you should see your username in the upper-right corner of the page.
Click your username and the website will open up a drop-down menu. From there, choose "Your logbook." The first screenshot for this step shows red line(s) where your username will appear, and the drop down menu (once it is opened).
Now you should be on a webpage where you see your dive logs. In the URL of your web browser, you should see something like:
https://www.deepblu.com/user/{user id}/profile/divelogs
{user id} will be a 20-character random string of lower-case letters and numbers, and that is your user id.
Copy your user id somewhere so you can use it again in a moment. The second screenshot for this step shows and example of this, where part of the user id has been concealed for privacy reasons.
Step 3: Retrieve Your Log Data From the DeepBlu API
Now that you have your user id, you can use that information to make a request to retrieve your data. Here is how to do that:
Copy the following URL into a new tab in your web browser:
https://prodcdn.tritondive.co/apis/discover/v0/post/{userid}/diveLog?limit=1000&skip=0
Replace {userid} in the URL you copied into your web browser with the user id you retrieved from step 2.
Optional: If you only want data for some of your logs or if you have more than 1000 dive logs, you can change the numbers following "limit=" and "skip=" in the URL you copied into a web browser. For example, if you wanted only 50 logs, but wanted to skip the most recent 10 logs, your URL would look like:
https://prodcdn.tritondive.co/apis/discover/v0/post/{userid}/diveLog?limit=50&skip=10
Once you have hit "enter" and submitted the URL to your browser, you should get a response back with your data. Now, if you use File -> save (or Ctrl-S on Windows, ⌘-S on Mac), your browser should open up a save dialog for a file named "diveLog.json."
Save the file where you would like, and now you have access to all of your dive log information.
Continue with further steps if you would like to view your data in Microsoft Excel
Step 4: Open Microsoft Excel
Launch the Microsoft Excel application from your computer.
Step 5: Open Power Query Editor
Launch Power Query Editor. There are different ways to do this depending on which version of Excel you have. This tutorial will use Excel for Microsoft 365.
Type "Launch Power Query Editor" into the search bar at the top of Excel to bring up the Power Query Editor application.
If you would like more information about Power Query Editor, please see this link: https://support.microsoft.com/en-gb/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
Step 6: Load Your File Into Power Query Editor
Now that the Power Query Editor application is open, we need to load the file we downloaded from DeepBlu into the application.
Make sure the Home tab is selected and look to the upper-right side in the "New Query" section choose New Source, then File, then JSON (see the attached image).
When the open file dialog opens, navigate to where you saved the file downloaded from the DeepBlu website, select the file, and click the "Open" button.
The file has now been loaded.
Step 7: Open the Advanced Editor
Now that your data has been loaded, we can enter a query that will format everything so that we view the data in Excel.
Start by Clicking the "View" tab at the top of the Power Query Editor and click the "Advanced Editor" button.
Step 8: Copy Your Document Source
Once the advanced query editor window opens, the second line should say something like:
Source = Json.Document(File.Contents("{filepath}\diveLog.json"))
Where {filepath} is the path on your computer to the file that you opened in the previous step.
Copy that entire second line and save it somewhere so we can use it in the next step.
Step 9: Paste the Query Into the Advanced Editor
Delete the entire text of the Advanced Query Editor and paste in the following. After pasting the below, be sure to replace the second line with the document source you copied in the previous step and click the "Done" button:
let
Source = Json.Document(File.Contents("C:\Users\gaget\Downloads\diveLog.json")),
result = Source[result],
posts = result[posts],
#"Converted to Table" = Table.FromList(posts, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"postId", "diveLogId", "publishTime", "createTime", "updateTime", "content", "postType", "userId", "privacy", "orgPostId", "groupId", "postHeaderUrl", "commentCount", "likeCount", "shareCount", "ogUrl", "ogTitle", "ogImageUrl", "ogDescription", "sharedEntityId", "sharedImageUrl", "topicId", "delFlag", "isTrending", "comments", "badgeUrl", "badgeDescriptionUrl", "onLogTop", "viewCount", "title", "digest", "htmlContent", "isLive", "badgeText", "kind", "entityId", "contentWithMention", "checkInSpotId", "tags", "userName", "userImageUrl", "backgroundUrl", "userDiveType", "medias", "isLiked", "diveLog"}, {"Column1.postId", "Column1.diveLogId", "Column1.publishTime", "Column1.createTime", "Column1.updateTime", "Column1.content", "Column1.postType", "Column1.userId", "Column1.privacy", "Column1.orgPostId", "Column1.groupId", "Column1.postHeaderUrl", "Column1.commentCount", "Column1.likeCount", "Column1.shareCount", "Column1.ogUrl", "Column1.ogTitle", "Column1.ogImageUrl", "Column1.ogDescription", "Column1.sharedEntityId", "Column1.sharedImageUrl", "Column1.topicId", "Column1.delFlag", "Column1.isTrending", "Column1.comments", "Column1.badgeUrl", "Column1.badgeDescriptionUrl", "Column1.onLogTop", "Column1.viewCount", "Column1.title", "Column1.digest", "Column1.htmlContent", "Column1.isLive", "Column1.badgeText", "Column1.kind", "Column1.entityId", "Column1.contentWithMention", "Column1.checkInSpotId", "Column1.tags", "Column1.userName", "Column1.userImageUrl", "Column1.backgroundUrl", "Column1.userDiveType", "Column1.medias", "Column1.isLiked", "Column1.diveLog"}),
#"Expanded Column1.diveLog" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.diveLog", {"_id", "_DiveCondition", "_DiveGear", "airPressure", "appVersion", "diveDTRaw", "diveDuration", "diveMaxDepth", "diveMinTemperature", "diveSampleInterval", "diveType", "groupIndex", "groupTotal", "hide", "key", "macAddress", "ownerId", "sourceType", "status", "version", "waterType", "lastAndroidModifyDT", "lastAndroidModifyVersion", "diveInType", "dataVerify", "createDT", "modifyDT", "diveBuddiesInvited", "diveBuddiesWaitList", "diveComputer", "lastAndroidModifyDTUTC", "diveDTRawUTC", "createDTUTC", "modifyDTUTC", "divePurposes", "diveSpotId", "isTrainingLog", "divePurpose", "postId", "notes", "privacySetting", "diveSpotName", "diveSiteName", "diveDT", "diveDTUTC", "_geoLocation", "postDT", "postDTUTC", "_diveProfile", "diveRawHEX", "divelogId", "diveTime", "diveBuddiesObj", "diveBuddiesWaitListObj", "medias", "divespot", "entityIcon", "entityId"}, {"Column1.diveLog._id", "Column1.diveLog._DiveCondition", "Column1.diveLog._DiveGear", "Column1.diveLog.airPressure", "Column1.diveLog.appVersion", "Column1.diveLog.diveDTRaw", "Column1.diveLog.diveDuration", "Column1.diveLog.diveMaxDepth", "Column1.diveLog.diveMinTemperature", "Column1.diveLog.diveSampleInterval", "Column1.diveLog.diveType", "Column1.diveLog.groupIndex", "Column1.diveLog.groupTotal", "Column1.diveLog.hide", "Column1.diveLog.key", "Column1.diveLog.macAddress", "Column1.diveLog.ownerId", "Column1.diveLog.sourceType", "Column1.diveLog.status", "Column1.diveLog.version", "Column1.diveLog.waterType", "Column1.diveLog.lastAndroidModifyDT", "Column1.diveLog.lastAndroidModifyVersion", "Column1.diveLog.diveInType", "Column1.diveLog.dataVerify", "Column1.diveLog.createDT", "Column1.diveLog.modifyDT", "Column1.diveLog.diveBuddiesInvited", "Column1.diveLog.diveBuddiesWaitList", "Column1.diveLog.diveComputer", "Column1.diveLog.lastAndroidModifyDTUTC", "Column1.diveLog.diveDTRawUTC", "Column1.diveLog.createDTUTC", "Column1.diveLog.modifyDTUTC", "Column1.diveLog.divePurposes", "Column1.diveLog.diveSpotId", "Column1.diveLog.isTrainingLog", "Column1.diveLog.divePurpose", "Column1.diveLog.postId", "Column1.diveLog.notes", "Column1.diveLog.privacySetting", "Column1.diveLog.diveSpotName", "Column1.diveLog.diveSiteName", "Column1.diveLog.diveDT", "Column1.diveLog.diveDTUTC", "Column1.diveLog._geoLocation", "Column1.diveLog.postDT", "Column1.diveLog.postDTUTC", "Column1.diveLog._diveProfile", "Column1.diveLog.diveRawHEX", "Column1.diveLog.divelogId", "Column1.diveLog.diveTime", "Column1.diveLog.diveBuddiesObj", "Column1.diveLog.diveBuddiesWaitListObj", "Column1.diveLog.medias", "Column1.diveLog.divespot", "Column1.diveLog.entityIcon", "Column1.diveLog.entityId"}),
#"Expanded Column1.diveLog._DiveCondition" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog", "Column1.diveLog._DiveCondition", {"current", "minWaterTemperature", "visibility", "wave", "weather", "averageDepth"}, {"Column1.diveLog._DiveCondition.current", "Column1.diveLog._DiveCondition.minWaterTemperature", "Column1.diveLog._DiveCondition.visibility", "Column1.diveLog._DiveCondition.wave", "Column1.diveLog._DiveCondition.weather", "Column1.diveLog._DiveCondition.averageDepth"}),
#"Expanded Column1.diveLog._DiveGear" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog._DiveCondition", "Column1.diveLog._DiveGear", {"BCD", "airMix", "airTank", "camera", "cameraHousing", "cameraLens", "cameraLight", "cameraStrobe", "diveComputer", "endBar", "fins", "lightTorch", "regulator", "startedBar", "suitType", "thickness", "weight"}, {"Column1.diveLog._DiveGear.BCD", "Column1.diveLog._DiveGear.airMix", "Column1.diveLog._DiveGear.airTank", "Column1.diveLog._DiveGear.camera", "Column1.diveLog._DiveGear.cameraHousing", "Column1.diveLog._DiveGear.cameraLens", "Column1.diveLog._DiveGear.cameraLight", "Column1.diveLog._DiveGear.cameraStrobe", "Column1.diveLog._DiveGear.diveComputer", "Column1.diveLog._DiveGear.endBar", "Column1.diveLog._DiveGear.fins", "Column1.diveLog._DiveGear.lightTorch", "Column1.diveLog._DiveGear.regulator", "Column1.diveLog._DiveGear.startedBar", "Column1.diveLog._DiveGear.suitType", "Column1.diveLog._DiveGear.thickness", "Column1.diveLog._DiveGear.weight"}),
#"Expanded Column1.diveLog._DiveGear.airTank" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog._DiveGear", "Column1.diveLog._DiveGear.airTank", {"material", "volume"}, {"Column1.diveLog._DiveGear.airTank.material", "Column1.diveLog._DiveGear.airTank.volume"}),
#"Expanded Column1.diveLog._DiveGear.regulator" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog._DiveGear.airTank", "Column1.diveLog._DiveGear.regulator", {"firstStage", "secondStage"}, {"Column1.diveLog._DiveGear.regulator.firstStage", "Column1.diveLog._DiveGear.regulator.secondStage"}),
#"Expanded Column1.diveLog._geoLocation" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog._DiveGear.regulator", "Column1.diveLog._geoLocation", {"coordinates", "type"}, {"Column1.diveLog._geoLocation.coordinates", "Column1.diveLog._geoLocation.type"}),
#"Expanded Column1.diveLog.divespot" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog._geoLocation", "Column1.diveLog.divespot", {"_id", "spottype", "createDT", "createUser", "divespot", "divesite", "region", "country", "continent", "_geoLocation", "gpsLocation", "countryCode", "modifyDT", "createDTUTC", "modifyDTUTC", "hide", "logCount", "mediaCount", "regionId", "imageUrl", "mediaId", "sharedImage", "staticMap", "staticMarker", "features", "timezone", "ratingStar1", "ratingStar2", "ratingStar3", "ratingStar4", "ratingStar5", "ratingAvg", "popularity", "recommendCount", "ratingCount"}, {"Column1.diveLog.divespot._id", "Column1.diveLog.divespot.spottype", "Column1.diveLog.divespot.createDT", "Column1.diveLog.divespot.createUser", "Column1.diveLog.divespot.divespot", "Column1.diveLog.divespot.divesite", "Column1.diveLog.divespot.region", "Column1.diveLog.divespot.country", "Column1.diveLog.divespot.continent", "Column1.diveLog.divespot._geoLocation", "Column1.diveLog.divespot.gpsLocation", "Column1.diveLog.divespot.countryCode", "Column1.diveLog.divespot.modifyDT", "Column1.diveLog.divespot.createDTUTC", "Column1.diveLog.divespot.modifyDTUTC", "Column1.diveLog.divespot.hide", "Column1.diveLog.divespot.logCount", "Column1.diveLog.divespot.mediaCount", "Column1.diveLog.divespot.regionId", "Column1.diveLog.divespot.imageUrl", "Column1.diveLog.divespot.mediaId", "Column1.diveLog.divespot.sharedImage", "Column1.diveLog.divespot.staticMap", "Column1.diveLog.divespot.staticMarker", "Column1.diveLog.divespot.features", "Column1.diveLog.divespot.timezone", "Column1.diveLog.divespot.ratingStar1", "Column1.diveLog.divespot.ratingStar2", "Column1.diveLog.divespot.ratingStar3", "Column1.diveLog.divespot.ratingStar4", "Column1.diveLog.divespot.ratingStar5", "Column1.diveLog.divespot.ratingAvg", "Column1.diveLog.divespot.popularity", "Column1.diveLog.divespot.recommendCount", "Column1.diveLog.divespot.ratingCount"}),
#"Expanded Column1.diveLog.divespot._geoLocation" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog.divespot", "Column1.diveLog.divespot._geoLocation", {"type", "coordinates"}, {"Column1.diveLog.divespot._geoLocation.type", "Column1.diveLog.divespot._geoLocation.coordinates"}),
#"Expanded Column1.diveLog.divespot.gpsLocation" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog.divespot._geoLocation", "Column1.diveLog.divespot.gpsLocation", {"lat", "lng"}, {"Column1.diveLog.divespot.gpsLocation.lat", "Column1.diveLog.divespot.gpsLocation.lng"}),
#"Expanded Column1.diveLog.divespot.timezone" = Table.ExpandRecordColumn(#"Expanded Column1.diveLog.divespot.gpsLocation", "Column1.diveLog.divespot.timezone", {"dstOffset", "rawOffset", "timeZoneId", "timeZoneName", "offset", "timeZoneupdateUTC"}, {"Column1.diveLog.divespot.timezone.dstOffset", "Column1.diveLog.divespot.timezone.rawOffset", "Column1.diveLog.divespot.timezone.timeZoneId", "Column1.diveLog.divespot.timezone.timeZoneName", "Column1.diveLog.divespot.timezone.offset", "Column1.diveLog.divespot.timezone.timeZoneupdateUTC"})
in
#"Expanded Column1.diveLog.divespot.timezone"
Step 10: Close Power Query Editor and Load Data Into Excel
You should now see a table with a lot of columns and as many rows as you have dive logs. The next step is to close the Power Query Editor and Load the data into Excel.
On the top menu, click the "Home" tab followed by the "Close & Load" button.
Clicking this will bring up the data in a table in Excel.
Step 11: Save the File
Now you can save the file as an Excel document that you can open any time. Use File -> Save As or hit the Save Icon in Excel to bring up a dialog to save the file. Save the file to wherever you would like. Now you have a copy of your data from your dive computer.
Step 12: Understanding and Converting the Data
It's important to understand that the data is stored in metric units, so any values will have to be converted to imperial units, if that's your preference.
There are also probably many more fields than you really want, and there might be some that you find missing. In the query from step 9 above, any value that was considered a list was not expanded because then you would have multiple rows for each dive log. Any time you see a cell with the value "List," that means there is more data available, but you'll have to use the Power Query editor to expand that on your own.
Below are value conversion considerations:
Time (for example: publishTime, createTime, diveDuration):
Values are stored in seconds, but some need additional processing to convert.
For numbers that are pretty long (more than 10 digits), they represent seconds since 1/1/1970. You will need to use the following formula in Excel (the following assumes the time data you want to convert is stored in cell A2):
=(A2/86400000)+25569
Note that this is approximate because it does not account for the time zone.
To convert from seconds (diveDuration is stored in seconds) to minutes, simply divide by 60. A formula to convert a time stored in cell A2 from seconds to minutes (and round to two decimal places) is shown below:
=ROUND(A2/60,2)
Temperature (for example: minWaterTemperature):
These values are stored in Celsius and multiplied by 10. So if the value reads 212, that means 21.2 degrees C (or 70.2 F, rounded). A formula to convert a temperature in cell A2 would look like:
=CONVERT(A2/10,"C","F")
Pressure (for example: startedBar, endBar)
These values are store in Bar. A formula to convert a pressure to PSI (and round to the nearest whole number) in cell A2 would look like:
=ROUND(A2*14.5038,0)
Depth (for example: averageDepth)
These values are stored in cm, but to calculate the true value, you must subtract the baseline "depth" the computer determined for the air, as well. So if your averageDepth value reads 1460 and your airPressure value reads 980, then your average depth is 1460 - 980 = 480 cm (or 4.8 m). To convert to feet, you then need to divide by 30.48 (the number of cm in one foot).
A formula to convert depth to ft in cell A2, where the airPressure value is stored in B2 (and round to two decimal places) would look like:
=ROUND((A2-B2)/30.48,2)
Volume (for example: volume)
These values are stored in liters. To convert to cubic feet, multiply by 28.3168.
A formula to convert volume to cubic feet in cell A2 (and round to nearest whole number) would look like:
=ROUND(A2*28.3168,0)
Weight
These values are stored in kgs. It appears that the conversion to lbs (if you entered your weight in lbs) is simply to multiply by 2 (usually kgs -> lbs is more precise at ~2.2lbs / kg). A formula to convert weight to lbs in cell A2 would look like:
=A2*2
