Excel Integration
Import CaneToad data directly into Microsoft Excel
Excel Integration
Import mining data into Microsoft Excel using Power Query or direct web queries.
Method 1: Power Query (Recommended)
Power Query provides the most flexible way to import and transform API data.
Basic Setup
Open Power Query
In Excel, go to Data → Get Data → From Web.
Configure the URL
Enter your API URL:
https://api.canetoad.ai/api/v1/companies?format=csv&per_page=100&api_key=ct_your_tokenClick OK to connect.
Load Data
In the preview, click Load to import directly, or Transform Data to modify the data first.
Advanced: Header Authentication
For better security, use header-based authentication:
Create a Blank Query
Go to Data → Get Data → From Other Sources → Blank Query.
Enter M Code
Open the Advanced Editor and paste:
let
// Configuration
apiKey = Excel.CurrentWorkbook(){[Name="ApiKey"]}[Content]{0}[Column1],
baseUrl = "https://api.canetoad.ai/api/v1/companies",
// Build request
source = Json.Document(
Web.Contents(
baseUrl,
[
Query = [per_page = "100", page = "1"],
Headers = [
Authorization = "Bearer " & apiKey,
Accept = "application/json"
]
]
)
),
// Extract data array
data = source[data],
// Convert to table
table = Table.FromList(data, Splitter.SplitByNothing()),
expanded = Table.ExpandRecordColumn(table, "Column1",
{"asx_code", "company_name", "market_cap_aud", "gics_sector", "total_tenements", "is_active"})
in
expandedCreate API Key Cell
- In a cell (e.g., A1 in a "Config" sheet), enter your API key
- Name the range "ApiKey" (Formulas → Define Name)
- Hide this sheet for security
Load the Query
Click Close & Load to import the data.
Method 2: CSV Import with Refresh
Direct CSV Import
Create Data Connection
Go to Data → From Web and enter:
https://api.canetoad.ai/api/v1/companies?format=csv&per_page=100&api_key=ct_your_tokenConfigure Connection
When prompted, select:
- Delimiter: Comma
- Data Type Detection: Based on entire dataset
Set Up Refresh
Right-click the query in Queries & Connections panel:
- Enable Refresh every X minutes
- Enable Refresh data when opening the file
Method 3: VBA Macro
For complete control, use VBA to fetch and process data.
Sub ImportCaneToadData()
Dim http As Object
Dim json As Object
Dim apiKey As String
Dim url As String
Dim response As String
Dim ws As Worksheet
Dim data As Object
Dim item As Object
Dim row As Long
' Configuration
apiKey = ThisWorkbook.Sheets("Config").Range("A1").Value
url = "https://api.canetoad.ai/api/v1/companies?per_page=100"
' Create HTTP request
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.setRequestHeader "Authorization", "Bearer " & apiKey
http.setRequestHeader "Accept", "application/json"
http.send
' Check response
If http.Status <> 200 Then
MsgBox "API Error: " & http.Status & " - " & http.responseText
Exit Sub
End If
response = http.responseText
' Parse JSON (requires JsonConverter module)
Set json = JsonConverter.ParseJson(response)
Set data = json("data")
' Write to sheet
Set ws = ThisWorkbook.Sheets("Companies")
ws.Cells.Clear
' Headers
ws.Range("A1:F1").Value = Array("ASX Code", "Company Name", "Market Cap", "Sector", "Tenements", "Active")
' Data
row = 2
For Each item In data
ws.Cells(row, 1).Value = item("asx_code")
ws.Cells(row, 2).Value = item("company_name")
ws.Cells(row, 3).Value = item("market_cap_aud")
ws.Cells(row, 4).Value = item("gics_sector")
ws.Cells(row, 5).Value = item("total_tenements")
ws.Cells(row, 6).Value = item("is_active")
row = row + 1
Next
' Format
ws.Columns("A:F").AutoFit
MsgBox "Imported " & (row - 2) & " companies"
End SubThe VBA method requires the VBA-JSON library for JSON parsing.
Pagination for Large Datasets
Power Query Pagination
let
// Configuration
apiKey = "ct_your_token",
baseUrl = "https://api.canetoad.ai/api/v1/companies",
perPage = 100,
// Function to fetch a page
GetPage = (page as number) =>
let
source = Json.Document(
Web.Contents(
baseUrl,
[
Query = [per_page = Text.From(perPage), page = Text.From(page)],
Headers = [Authorization = "Bearer " & apiKey]
]
)
)
in
source,
// Get first page to determine total pages
firstPage = GetPage(1),
totalPages = firstPage[meta][total_pages],
// Generate list of page numbers
pageNumbers = {1..totalPages},
// Fetch all pages
allPages = List.Transform(pageNumbers, each GetPage(_)[data]),
// Combine all data
combinedData = List.Combine(allPages),
// Convert to table
table = Table.FromList(combinedData, Splitter.SplitByNothing()),
expanded = Table.ExpandRecordColumn(table, "Column1",
{"asx_code", "company_name", "market_cap_aud", "gics_sector", "total_tenements", "is_active"})
in
expandedSchedule Automatic Refresh
Windows Task Scheduler
Create a PowerShell script to refresh Excel:
# refresh-canetoad.ps1
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\Path\To\YourWorkbook.xlsx")
$workbook.RefreshAll()
Start-Sleep -Seconds 30 # Wait for refresh
$workbook.Save()
$workbook.Close()
$excel.Quit()Schedule with Task Scheduler to run daily.
Troubleshooting
Best Practices
- Use Power Query for most use cases
- Store API keys in a hidden config sheet
- Set up automatic refresh for dashboards
- Use pagination for datasets over 100 records
- Implement error handling for production workbooks