Return to Answer

Here's a simplified macro that works as far back as Excel 2007, original credit Samir Khan:

Sub GetBTCUSD()

Range("BTCUSD").Select
Selection.ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _
     "URL;https://api.bitcoinaverage.com/ticker/USD/last", Destination:=Range("BTCUSD"))
    .Name = "BTCUSD field that will pollute your Name Manager but I don't know how to get rid of"
    .AdjustColumnWidth = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = False
    .RefreshPeriod = 0
    .WebSingleBlockTextImport = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

End Sub

To use the macro:

1. Name a column in your spreadsheet "BTCUSD" (just click in the Name Box in the top left and type "BTCUSD"
2. Add a button to the sheet (you can name it "Get BTCUSD")
3. Create a new Macro and paste the code above
4. Right click the button, choose Assign macro, and pick the GetBTCUSD macro.

Here's a simplified macro that works as far back as Excel 2007, original credit Samir Khan:

Sub GetBTCUSD()

Range("BTCUSD").Select
Selection.ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _
     "URL;https://api.bitcoinaverage.com/ticker/USD/last", Destination:=Range("BTCUSD"))
    .Name = "BTCUSD field that will pollute your Name Manager but I don't know how to get rid of"
    .AdjustColumnWidth = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SaveData = False
    .RefreshPeriod = 0
    .WebSingleBlockTextImport = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

End Sub

To use the macro:

1. Name a column in your spreadsheet "BTCUSD" (just click in the Name Box in the top left and type "BTCUSD"
2. Add a button to the sheet (you can name it "Get BTCUSD")
3. Create a new Macro and paste the code above
4. Right click the button, choose Assign macro, and pick the GetBTCUSD macro.

Here's a macro that works as far back as Excel 2007, credit Samir Khan:

Sub GetBTCUSD()

Range("BTCUSD").Select
Selection.ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _
     "URL;https://api.bitcoinaverage.com/ticker/USD/last", Destination:=Range("BTCUSD"))
    .Name = "BTCUSD field that will pollute your Name Manager but I don't know how to get rid of"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = """table1"""
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

End Sub

To use the macro:

1. Name a column in your spreadsheet "BTCUSD" (just click in the Name Box in the top left and type "BTCUSD"
2. Add a button to the sheet (you can name it "Get BTCUSD")
3. Create a new Macro and paste the code above
4. Right click the button, choose Assign macro, and pick the GetBTCUSD macro.

Here's a simplified macro that works as far back as Excel 2007, original credit Samir Khan:

Sub GetBTCUSD()

Range("BTCUSD").Select
Selection.ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _
     "URL;https://api.bitcoinaverage.com/ticker/USD/last", Destination:=Range("BTCUSD"))
    .Name = "BTCUSD field that will pollute your Name Manager but I don't know how to get rid of"
    .AdjustColumnWidth = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = False
    .RefreshPeriod = 0
    .WebSingleBlockTextImport = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

End Sub

To use the macro:

1. Name a column in your spreadsheet "BTCUSD" (just click in the Name Box in the top left and type "BTCUSD"
2. Add a button to the sheet (you can name it "Get BTCUSD")
3. Create a new Macro and paste the code above
4. Right click the button, choose Assign macro, and pick the GetBTCUSD macro.