フライトレーダーからダウンロードしたCSVをエクセルで使いやすくコンバートする

フライトレーダのSilver、Gold､Businnesプランだとサービスで機体番号や便名からフライトデーターを取得することができる。航空機がいつ、どこを飛行していたか日ごとに記録されたCSV形式のファイルを好きなだけダウンロードできる。

ダウンロードしてみるとCSVファイルは日本時間が分からなかったり、数字データばかりでちょっと読みにくい。そこでエクセルをつかって使いやすくコンバートしてみる。

CSVファイルの詳細

ダウンロードしたファイルをエクセルでひらくとこんな感じ。

Timestamp：UNIX時間
UTC：UTC時刻
Callsign：コールサイン
Position：緯度・経度
Altitude：高度
Speed：速度
Direction：方位

コンバートで改善した箇所

Timestamp

TimestampはUNIX時間。そのままの値だとエクセルの関数や書式設定で時刻を表示することができないので計算して日本時間(JST)にする。

UNIX時間をEXCELのシリアル値に変換にする式
(値+ 32400) / 86400 + 25569

Position

Positionは緯度と経度の10進値になっている。
Chromeに値をそのまま貼り付けるとグーグルマップにリンクしてくれるけど毎回、はりつけるものメンドクサイ。Googleマップお決まりのURLに緯度経度をくっつけてクリックしたらグーグルマップにリンクさせる。

Google Mapの緯度経度リンク
google.co.jp/maps/search/緯度,経度

Direction

0～360の値で方位を表している。
0=360(北)、90(東)、180(南)、270(西)

東西南北を16方向で表記したほうがわかりやすい。

CSVファイルをコンバートした結果

日本時間と進路が見やすくなった。
ポジションをクリックするとGoogleマップにリンクするもの便利だ。

CSVファイルをコンバートした結果

マクロはこんな感じ。Mac版エクセルでも問題なく動作する。

Dim ws As Worksheet
Dim 縦最大 As Long
Dim 縦 As Long

Sub FR24のCSVをいい感じに変換()

Set ws = ActiveSheet
縦最大 = ws.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For 縦 = 2 To 縦最大
    DoEvents
    '■■■　Unix → JST　■■■
    If ws.Cells(縦, 1).NumberFormatLocal <> "yyyy/m/d h:mm;@" Then
        ws.Cells(縦, 1) = (Cells(縦, 1) + 32400) / 86400 + 25569
        ws.Cells(縦, 1).NumberFormatLocal = "yyyy/m/d h:mm;@"
    End If

    '■■■　経度緯度 → Gマップ　■■■
    If ws.Cells(縦, 4).Hyperlinks.Count = 0 Then
        ws.Hyperlinks.Add Anchor:=Cells(縦, 4), Address:= _
        "http://google.co.jp/maps/search/" & Cells(縦, 4).Value, TextToDisplay:=Cells(縦, 4).Value
    End If

    '■■■　方位角 → 16方位　■■■
    If IsNumeric(ws.Cells(縦, 7)) Then
        Dim 方位角 As Long
        Dim 方位角16  As Variant
        Dim 方位(16) As Variant

        方位角 = Cells(縦, 7)
        方位(0) = "北"
        方位(1) = "北北東"
        方位(2) = "北東"
        方位(3) = "東北東"
        方位(4) = "東"
        方位(5) = "東南東"
        方位(6) = "南東"
        方位(7) = "南南東"
        方位(8) = "南"
        方位(9) = "南南西"
        方位(10) = "南西"
        方位(11) = "西南西"
        方位(12) = "西"
        方位(13) = "西北西"
        方位(14) = "北西"
        方位(15) = "北北西"
        方位(16) = "北"

        方位角16 = 方位角 / 22.5
        Cells(縦, 7) = 方位(方位角 / 22.5)
    End If

Next 縦

End Sub

航空ファンは空港や航空機の情報や航空無線の周波数など色々なデータをたくさん必要。
どんどん溜まっていくデータの管理にはエクセルが最適！
いいのができたらまた紹介していくぞ。