How to retrieve historical prices from AlphaVantage using C#
Introduction
This C# recipe shows how you can retrieve historical prices from AlphaVantage using their free API and writing the results to Excel
Note AlphaVantage API is free. However there are limits in terms of how many requests you can make. See AlphaVantage Free API Key'
Ingredients
The recipe uses the ServiceStack.Text Nuget package. You can install it using Nuget Package manager:
Install-Package ServiceStack.Text
From v4.0.62+ ServiceStack.Text is free!
C# Code Snippet
// retrieve monthly prices for Microsoft
var symbol = "MSFT";
// retrieve your free api key from https://www.alphavantage.co/support/#api-key
var apiKey = "demo";
// retrieve pries
var monthlyPrices =
$"https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY&symbol={symbol}&apikey={apiKey}&datatype=csv"
.GetStringFromUrl()
.FromCsv<List<AlphaVantageData>>();
// populate excel spreadsheet with rices
// Step 3
string Path = @"C:\Temp\ExcelRecipes\";
var fileName = "alphavantage.xlsx";
var fileInfo = new FileInfo(Path + fileName);
using (var excelFile = new ExcelPackage(fileInfo))
{
// add worksheet if not exists
if (!excelFile.Workbook.Worksheets.Any(ar => ar.Name == "Prices"))
excelFile.Workbook.Worksheets.Add("Prices");
var ws = excelFile.Workbook.Worksheets["Prices"];
// headers
ws.Cells[1, 1].Value = "Ticker";
ws.Cells[1, 2].Value = "Timestamp";
ws.Cells[1, 3].Value = "Open";
ws.Cells[1, 4].Value = "Close";
ws.Cells[1, 5].Value = "Low";
ws.Cells[1, 6].Value = "High";
ws.Cells[1, 7].Value = "Volume";
// content
int row = 2;
foreach (var price in monthlyPrices)
{
ws.Cells[row, 1].Value = symbol;
ws.Cells[row, 2].Value = price.Timestamp;
// apply date format style for Timestamp
ws.Cells[row, 2].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
ws.Cells[row, 3].Value = price.Open;
ws.Cells[row, 4].Value = price.Close;
ws.Cells[row, 5].Value = price.Low;
ws.Cells[row, 6].Value = price.High;
ws.Cells[row, 7].Value = price.Volume;
row++;
}
// format
excelFile.Save();
}
public class AlphaVantageData
{
public DateTime Timestamp { get; set; }
public decimal Open { get; set; }
public decimal High { get; set; }
public decimal Low { get; set; }
public decimal Close { get; set; }
public decimal Volume { get; set; }
}