capture live data at a specified time

Try this:

Private Sub Workbook_Open()

'Runs a macro at 4:30 PM
Application.OnTime TimeValue("16:30:00"), "StoreData"

End Sub

Sub StoreData()

NextRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets("Name of Destination Sheet").Cells(1,NextRow ).Value = Sheets("Name of Source Data Sheet").Range("A1").Value

End Sub

This will take data from the data sheet and put it on the destination sheet. 

If currently works to get data from A1 on the data sheet and put it on the next empty row in column A on the destination sheet.

I haven't been able to test this macro so try it on sample data first and expand it to get all necessary data.

Here are the tutorials from this site that you can use to create a macro like this or edit it further:

Automatically Run a Macro at a Certain Time

Find the Next Blank Row with VBA Macros in Excel

Get Data from the Worksheet into a Macro in Excel

Put Data into a Worksheet using a Macro in Excel