In previous articles, such as "Using Aria2 to Download GIS Data" and "Recommended Websites for Downloading Earthquake Data", the USGS (United States Geological Survey) was introduced as a source for global earthquake data. However, this website has a limitation: it will reject download requests if the query exceeds 2000 records. Therefore, a practical workaround is to construct download requests by month and download the data in batches. If I wanted to download all the data from 1900 until now, manually creating each download link would be very tedious. Hence, I came up with the following solution.

Problem Analysis

First, let's look at the download link:

https://earthquake.usgs.gov/fdsnws/event/1/query.csv?starttime=2023-01-05%2000:00:00&endtime=2024-01-12%2023:59:59&minmagnitude=1&orderby=time

There are two key parameters in the URL: starttime and endtime. The first represents the start time, and the second represents the end time. As long as we set these times correctly, we can generate the links accordingly. How can we generate them in bulk? One straightforward approach is to write a script, but not everyone knows how to code, and writing a script just for downloading data feels like using a cannon to kill a mosquito. So, I decided to use an old-school tool—Excel.

Using Excel for Bulk Construction

At the time of writing this article, I was using a Mac and opted for WPS instead of Excel (for small tasks like this, there isn't much difference). Here, we'll demonstrate how to create download links for the years 2022 to 2023.

Note that the dates aren't entered one by one; refer to the animated GIF below for details:

Next, we define a function:

=CONCAT("https://earthquake.usgs.gov/fdsnws/event/1/query.csv?starttime=",TEXT(A1,"yyyy-mm-dd"),"%2000:00:00&endtime=",TEXT(B1,"yyyy-mm-dd"),"%2023:59:59&minmagnitude=1&orderby=time")

Finally, as shown in the animated GIF above, simply drag the formula down, and all the download links will be generated automatically:

The rest of the work can be handed over to tools like Thunderbird, IDM, or Aria2, as described in the article "Using Aria2 to Download GIS Data", which allows for batch downloads.

Closing Thoughts

Don't underestimate Excel—it can often outperform programming in terms of speed and efficiency when used skillfully, as demonstrated in this article: "GIS Tutorial": How to Obtain POI Data Without Programming.

As always, my advice is to focus on achieving your goal, rather than being fixated on using programming. After all, both are merely tools.