无VBA经验，如何自动记录API Ticker数据至Excel表格？若需代码请提供

Hey there! Let's break down how you can automatically preserve your daily API ticker data—no coding required if possible, and if you do need to dip into VBA, I'll walk you through every step since you're new to it.

If you want to avoid macros entirely, these two methods should work for most cases:

• Power Query + Excel Tables (Most Reliable)
  Power Query lets you refresh API data and append new entries to your existing historical table instead of overwriting it. Here's how to set it up:

  1. Turn your pre-made 2018 date range into an official Excel table: select your date range, press Ctrl+T, and check "My table has headers".
  2. Go to the Data tab, select Get Data > From Web (or the relevant source for your API), and import your API data into the Power Query Editor.
  3. Clean up the API data in the editor to match the columns (date + ticker value) of your historical table.
  4. Click Close & Load To, choose Only Create Connection, then right-click the new connection > Properties. Check "Append data to existing table" and select your historical table as the target.
  5. Set up auto-refresh: Go to Data > Refresh All > Connection Properties, then set a refresh frequency (e.g., daily) to pull and save new data automatically.

• Circular References (Quick, But Use With Caution)
  This is a hacky but simple option for basic daily tracking. Note: Enable iterative calculation first:

  1. Go to File > Options > Formulas, check "Enable iterative calculation", and set the number of iterations to 1.
  2. Suppose your API's current date is in A2 and value in B2, and your historical data starts at A5.
     • In A5, enter: =IF(A2<>"",IF(COUNTIF($A$5:A5,A2)=0,A2,""),"") then drag down to cover your 2018 date range.
     • In B5, enter: =IF(A5<>"",IF(B2<>"",B2,""),"") then drag down.
       This will only populate a date/value if it's new, but it can glitch if the API refreshes multiple times in a day.

If the no-code methods don't fit your API setup, here's a beginner-friendly VBA macro that appends new API data to your history without overwriting old entries.

First, Prep Your Workbook: #

• Let’s say your API returns the current date in Sheet1!A1 and ticker value in Sheet1!B1.
• Your historical data is stored in Sheet2, with headers in row 1 (A1: "Date", B1: "Ticker Value") and historical entries starting at row 2.

Set Up the Macro: #

1. Press Alt+F11 to open the VBA Editor.
2. Right-click your workbook in the left pane > Insert > Module.
3. Paste this code into the module:

Sub SaveAPIData()
    ' Define variables to reference our sheets and data
    Dim wsAPI As Worksheet
    Dim wsHistory As Worksheet
    Dim lastRow As Long
    Dim todayDate As Date
    Dim todayValue As Variant
    
    ' Point to your actual worksheets (update names if yours are different)
    Set wsAPI = ThisWorkbook.Worksheets("Sheet1") ' Sheet with fresh API data
    Set wsHistory = ThisWorkbook.Worksheets("Sheet2") ' Sheet with historical data
    
    ' Grab today's date and value from the API sheet
    todayDate = wsAPI.Range("A1").Value
    todayValue = wsAPI.Range("B1").Value
    
    ' Check if this date is already in the history to avoid duplicates
    If WorksheetFunction.CountIf(wsHistory.Range("A:A"), todayDate) = 0 Then
        ' Find the next empty row in the history sheet
        lastRow = wsHistory.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        ' Write the new date and value to the history sheet
        wsHistory.Cells(lastRow, "A").Value = todayDate
        wsHistory.Cells(lastRow, "B").Value = todayValue
        
        ' Optional: Auto-save the workbook to lock in the new data
        ThisWorkbook.Save
    End If
End Sub

What Each Part Does: #

• Sub SaveAPIData(): This is the name of our macro—you can rename it if you want.
• Dim ...: Creates variables to make it easier to reference our sheets and data.
• Set wsAPI = ...: Links the variable to your sheet with fresh API data (update the sheet name if yours isn't "Sheet1").
• CountIf: Checks if the current date already exists in the history sheet so we don't save duplicates.
• lastRow = ...: Finds the next empty row in the history sheet so new data is added to the bottom, not overwriting old entries.
• ThisWorkbook.Save: Automatically saves the workbook after adding new data (remove this line if you don't want auto-save).

Make the Macro Run Automatically: #

• Run when you open the workbook: Double-click ThisWorkbook in the left VBA pane, select Workbook from the top-left dropdown, then Open from the top-right. Paste this code:

  Private Sub Workbook_Open()
      Call SaveAPIData ' Runs our macro every time you open the file
  End Sub
  

• Run at a specific time daily: Add this code to your module to schedule a daily run (e.g., 3 PM):

  Sub ScheduleDailySave()
      Dim runTime As Date
      runTime = TimeValue("15:00:00") ' Set your desired time here
      Application.OnTime runTime, "SaveAPIData"
  End Sub
  

  Then add Call ScheduleDailySave to the Workbook_Open sub to set up the schedule each time you open the file.

Important Note: #

Save your workbook as an Excel Macro-Enabled Workbook (.xlsm)—otherwise, the macro will be lost when you close the file.

内容的提问来源于stack exchange，提问作者Klis