- #1
jumpjack
- 222
- 3
I wrote this small Excel macro to gather data from vehicles database:
Data source:
http://www.carqueryapi.com/documentation/api-usage/
Example query:
http://www.carqueryapi.com/api/0.3/?callback=?&cmd=getTrims&make=fiat
Macro works fine... but now I need something more powerful than Excel to analyse data!
For example, I created an additional column showing Nm/kg ratio and another one showing W/kg ratio; if I create a scatter plot between these columns, I get a multi-dots plot where I can apparently identify three groups of data; I'd like to be able to select those groups to figure out to which vehicles they match, or click on a single dot and see which vehichle it represents, or delete single dots from the chart. Excel does not help in this.
Another interesting scatter plot is Nm/kg vs "seconds to 100kph", which higlights a group of vehicles I'd like to identify.
Which SW do you usually use on Windows to perform advanced data analysis like this?
Code:
Sub ImportCarQueryDB()
Call JSON2Excel("2,3,4,8,12,14,20,21,26,27,28,29,31,32,33") ' Specify which fields to extract/import
End Sub
Sub JSON2Excel(ByVal fields As String)
Filename$ = "C:\temp\fiat2.txt"
Open Filename$ For Input As #1
contents = Input(LOF(1), 1) ' Legge intero file
FillNull$ = Replace(contents, "null", """""") ' sotituisce campi vuoti
contents = Replace(Replace(FillNull$, "]});", ""), "?({""Trims"":[", "") ' elimina marcatori inizio-fine
DestRow = 2
FirstTime = True
While InStr(contents, ":") > 0
InizioRecord = InStr(contents, "{")
FineRecord = InStr(contents, "}")
rowtext = Mid$(contents, InizioRecord, FineRecord - InizioRecord + 1) ' estrapola singola riga
Debug.Print rowtext
contents = Replace(contents, rowtext, "")
DestCol = 1
FieldNumber = 1
While InStr(rowtext, """") > 0
FirstQuote = InStr(rowtext, """")
SecondQuote = InStr(FirstQuote + 1, rowtext, """")
ThirdQuote = InStr(SecondQuote + 1, rowtext, """")
FourthQuote = InStr(ThirdQuote + 1, rowtext, """")
If IncludeField(FieldNumber, fields) = True Then
FieldValue = Mid$(rowtext, ThirdQuote, FourthQuote - ThirdQuote + 1) ' valore campo
If FirstTime = True Then
FieldName = Mid$(rowtext, FirstQuote + 1, SecondQuote - FirstQuote - 1)
Cells(1, DestCol) = FieldName
End If
Cells(DestRow, DestCol) = Mid$(FieldValue, 2, Len(FieldValue) - 2) ' memorizza campo in foglio
DestCol = DestCol + 1
End If
CutField = Mid$(rowtext, FirstQuote, FourthQuote - FirstQuote + 1) ' stringa-campo da eliminare da riga
rowtext = Replace(rowtext, CutField, "") ' elimina stringa-campo da riga
FieldNumber = FieldNumber + 1
Wend
DestRow = DestRow + 1
Wend
FirstTime = False
Close #1
End Sub
Function IncludeField(ByVal i As Integer, ByVal s As String) As Boolean
s = s & ","
temp = False
While InStr(s, ",") > 0
FieldNumber = Mid$(s, 1, InStr(s, ",") - 1)
'DeleteField = Mid$(s, 1, InStr(s, ","))
s = Mid$(s, InStr(s, ",") + 1, Len(s) - InStr(s, ","))
If Val(FieldNumber) = i Then
IncludeField = True
Exit Function
End If
Wend
End Function
Data source:
http://www.carqueryapi.com/documentation/api-usage/
Example query:
http://www.carqueryapi.com/api/0.3/?callback=?&cmd=getTrims&make=fiat
Macro works fine... but now I need something more powerful than Excel to analyse data!
For example, I created an additional column showing Nm/kg ratio and another one showing W/kg ratio; if I create a scatter plot between these columns, I get a multi-dots plot where I can apparently identify three groups of data; I'd like to be able to select those groups to figure out to which vehicles they match, or click on a single dot and see which vehichle it represents, or delete single dots from the chart. Excel does not help in this.
Another interesting scatter plot is Nm/kg vs "seconds to 100kph", which higlights a group of vehicles I'd like to identify.
Which SW do you usually use on Windows to perform advanced data analysis like this?