Using Batch Files and Tasks with gsqlcmd

Using Batch Files and Tasks with gsqlcmd

Let's automate the following task:

  • Download 1-minute stock prices from Yahoo Finance for a list of tickers.

The basic URL looks like this:

https://query1.finance.yahoo.com/v7/finance/chart/AAPL?range=1d&interval=1m&indicators=quote&includeTimestamps=true

The symbols.txt file contains a list of tickers:

AAPL
FB
GOOGL

Using Batch Files

Here is a simple batch file to get the data for the list of tickers from the tickers.txt file:

@echo off

for /F %%i in (symbols.txt) do (

    gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/%%i?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
            %%i.csv /add=Symbol=%%i ^
            /datetimeformat=yyyy-MM-dd /echoUrl

    gsqlcmd sleep 300
)

Use the sleep mode to make a delay between requests to prevent banning your IP by web servers.

The batch file downloads three files: AAPL.CSV, FB.CSV, and GOOGL.CSV.

You can modify the batch file to download the data into a single output file:

@echo off

if exist data.csv del data.csv

for /F %%i in (symbols.txt) do (

    gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/%%i?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
            data.csv /append /add=Symbol=%%i ^
            /datetimeformat=yyyy-MM-dd /echoUrl

    gsqlcmd sleep 300
)

Delete the output file first and use the /append option to append data.

Using Task Files with /TaskFile Option

The /taskFile option allows solving such typical tasks using a single command.

Let's add a column name to symbols.txt:

Symbol
AAPL
FB
GOOGL

The final command uses the {symbol} parameter instead of the %%i variable:

@echo off

gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/{symbol}?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
        data.csv /add=Symbol={symbol} ^
        /datetimeformat=yyyy-MM-dd /echoUrl ^
        /delay=300 /taskFile=symbols.txt

Also, the command contains the /taskFile and /delay option (instead of the sleep mode) and does not contain the /append option.

gsqlcmd Enterprise allows getting task values from a database using the form like <connection>::<query>.

For example, you can change symbols.txt to a SELECT query:

@echo off

gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/{symbol}?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
        data.csv /add=Symbol={symbol} ^
        /datetimeformat=yyyy-MM-dd /echoUrl ^
        /delay=300 "/taskfile=db::SELECT Symbol FROM dbo.symbols"

Using Positional Task Files

You can use task files instead of positional parameters.

For example, you can place URLs into a file like urls.txt:

https://www.savetodb.com
https://www.savetodb.com/buy.htm

and use the command:

gsqlcmd download @urls.txt *.* /echoUrl

The download command requires two positional parameters.

In this example, gsqlcmd reads the first parameter from the task file and uses the second command line parameter.

You can place URLs and output files into the file:

https://www.savetodb.com         index.htm
https://www.savetodb.com/buy.htm buy.htm

and use a command with a single task file:

gsqlcmd download @urls.txt /echoUrl

The task files above do not contain headers.

So, gsqlcmd uses the first value as the first parameter and the second value as the second parameter.

You can create task files with headers.

For example:

URL                              FileName
https://www.savetodb.com         index.htm
https://www.savetodb.com/buy.htm buy.htm

Use URL, FileName, and OutputFileName to define positional parameter values.

You can also add additional columns used as values of the /set option.