Connect to Excel will speed up your analysis and save time downloading data. You can then make bespoked reports and models which you can save and return to at a later date. The process of setting this up may require the help of your IT department, particularly if there is a firewall protection. Otherwise the process of setting up is straight forward. Contact us if you need further guidence.
Step 1 – Download ODBC Driver
Go to this weblink https://dev.mysql.com/downloads/connector/odbc/ and download relevant driver. Normally 64 bit. I’d use the MSI installer rather than the ZIP version.
Then click, “No thanks just start my download”. Then install the driver.
Step 2 – Setup Driver
Open the “ODBC data sources” app by typing in the Windows search bar, bottom left. And you should see this box.
Click on the “System DNS” tab and click “Add”. You will see a list of drivers. Select “MySQL ODBC 5.3ANSI Driver”. You will then see this screen:
In `Data Source Name box` enter “TV”.
In `TCP/IP Server` box enter “126.96.36.199”
In User and Password enter the details we’ve sent to you separately (Note these are case sensitive).
Press “Test” to confirm the connection works then press “OK”.
Stage 3 – Set up Excel Connection
The instruction below are for Excel 2016. Locations of buttons may vary with other versions.
Open a fresh Excel workbook.
Go to the Data tab
Click Get external data “from other Sources”, then click “From Data Connection Wizard”
It then asks what type of data source you want to connect to. Click on “ODBC DSN” then Click Next
You will see a list of your ODBC data sources. Select “TV” which you created in stage 2. Click Next.
You then need to select database and table. In the dropdown box there are many database options. Select one of the following:
Tradevie_live – > “Dry cargo Arrivals”
Tradevie_live_export -> “Dry cargo Departures”
Tradevie_live_oil -> ”Tanker Departures”
Tradevie_live_oil_import ->” Tanker Arrivals”
You can then select the table or View. I recommend the views:
The difference between the Coastline versions is USA and Russia are split into Regions and are represented as countries so USA become “USA East Coast”, “USA West Coast” and ”USA Gulf Coast”. The Russian Federation becomes “Russia Baltic”, “Russia Black Sea”, “Russia Pacific” and “Russia Barents”. Annual data is back to the year 2000 and Monthly data is the last 3 years.
You will then have the option to view the data in Table, PivotTable Report or Pivot Chart. I’d Suggest Selecting Pivot Table. If you request Table, an Excel sheet is not large enough to view all the data for some tables.
You are now ready to play and build your pivot table. The Dry Cargo tables are several million lines long and it may table several minutes to refresh.
Option to reduce refresh rates
If you click on ‘properties’. You can select the tick box to `refresh data when opening the file’. This ensures your data is up to date.
If you’d like a sub-section of data to reduce refresh times, click the `definition tab`.
In the Command text: box you can extend the sequel command for example:
“SELECT * FROM `tradevie_live_oil`.`annual_dry_simple` where `Year` >=2015”
“SELECT * FROM `tradevie_live`.`annual_dry_simple` where `Group` = ‘’COAL”
“SELECT * FROM `tradevie_live_oil`.`annual_dry_simple` where `commodity` = ‘Iron ore’
Press okay and you will notice your data set is reduced but so is the time to refresh.