Ready to get started?

Learn more about the CData ADO.NET Provider for Quandl or download a free trial:

Download Now

Automate Quandl Integration Tasks from PowerShell

Are you looking for a quick and easy way to access Quandl data from PowerShell? We show how to use the Cmdlets for Quandl and the CData ADO.NET Provider for Quandl to connect to Quandl data and synchronize, automate, download, and more.

The CData Cmdlets for Quandl are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Quandl.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Quandl API, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Quandl data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Quandl. To access Quandl data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Quandl.

After obtaining the needed connection properties, accessing Quandl data in PowerShell consists of three basic steps.

Quandl uses an API key for authentication. See the help documentation for a guide to obtaining the APIKey property.

Additionally, set the DatabaseCode connection property to the code identifying the Database whose Datasets you want to query with SQL. You can search the available Databases by querying the Databases view.

PowerShell

  1. Install the module:

    Install-Module QuandlCmdlets
  2. Connect:

    $quandl = Connect-Quandl -APIKey "$APIKey" -DatabaseCode "$DatabaseCode"
  3. Search for and retrieve data:

    $collapse = "Daily" $aapl = Select-Quandl -Connection $quandl -Table "AAPL" -Where "Collapse = `'$Collapse`'" $aapl

    You can also use the Invoke-Quandl cmdlet to execute SQL commands:

    $aapl = Invoke-Quandl -Connection $quandl -Query 'SELECT * FROM AAPL WHERE Collapse = @Collapse' -Params @{'@Collapse'='Daily'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Quandl\lib\System.Data.CData.Quandl.dll")
  2. Connect to Quandl:

    $conn= New-Object System.Data.CData.Quandl.QuandlConnection("APIKey=abc123;DatabaseCode=WIKI;") $conn.Open()
  3. Instantiate the QuandlDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT Date, Volume from AAPL" $da= New-Object System.Data.CData.Quandl.QuandlDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.date $_.volume }

Update Quandl Data

PowerShell

Update-Quandl -Connection $Quandl -Columns @('Date','Volume') -Values @('MyDate', 'MyVolume') -Table AAPL -Id "MyID"

ADO.NET

$cmd = New-Object System.Data.CData.Quandl.QuandlCommand("UPDATE AAPL SET Collapse='Daily' WHERE ID = @myID", $conn) $cmd.Parameters.Add(new System.Data.CData.Quandl.QuandlParameter("@myID","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Quandl Data

PowerShell

Add-Quandl -Connection $Quandl -Table AAPL -Columns @("Date", "Volume") -Values @("MyDate", "MyVolume")

ADO.NET

$cmd = New-Object System.Data.CData.Quandl.QuandlCommand("INSERT INTO AAPL (Collapse) VALUES (@myCollapse)", $conn) $cmd.Parameters.Add(new System.Data.CData.Quandl.QuandlParameter("@myCollapse","Daily")) $cmd.ExecuteNonQuery()

Delete Quandl Data

PowerShell

Remove-Quandl -Connection $Quandl -Table "AAPL" -Id "MyID"

ADO.NET

$cmd = New-Object System.Data.CData.Quandl.QuandlCommand("DELETE FROM AAPL WHERE ID=@myID", $conn) $cmd.Parameters.Add(new System.Data.CData.Quandl.QuandlParameter("@myID","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()