Overview

With a wide variety of free APIs, many of them still have limits on how many times you can call them a day. Also, some may not offer access to historical data. In this example, we will call a free API for the Stock Price Data and store the results in a table.

Bonus. Part II. We will create and call a stored procedure with Entity Frameworks, to backup the data to an historical table and then delete/replace the data in the main table so it can be run on scheduler.

MS SQL Table

Create a table called StockPriceHistory .

CREATE TABLE [dbo].[PriceHistory](
	[Id] [int] NOT NULL,
	[TickerSymbol] [nvarchar](50) NULL,
	[ClosingPrice] [float] NULL,
	[High] [float] NULL,
	[Low] [float] NULL,
	[ClosingDate] [datetime2](7) NULL
);

ALTER TABLE [dbo].[PriceHistory]
   ADD Id INT IDENTITY

ALTER TABLE [dbo].[PriceHistory]
   ADD CONSTRAINT PK_PriceHistory
   PRIMARY KEY(Id);

Controller

The controller action below will get yesterday and todays date to get the last day of prices.

Libraries used for the controller

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Web.Http;
using System.Web.Http.Description;
using VirtualInvestorAPI.Models;
using System.Web.Http.Cors;
using Newtonsoft.Json.Linq;
using System.IO;

JSON DATA Format

JSON Data received.

{
  "symbol" : "AAPL",
  "historical" : [ {
    "date" : "2020-04-03",
    "open" : 242.8,
    "high" : 245.7,
    "low" : 238.97,
    "close" : 241.41,
    "adjClose" : 241.41,
    "volume" : 3.24182E7,
    "unadjustedVolume" : 3.24182E7,
    "change" : 1.39,
    "changePercent" : 0.572,
    "vwap" : 242.02667,
    "label" : "April 03, 20",
    "changeOverTime" : 0.00572
  } ]
}

CORS and Route Prefix

The controller uses attribute routing (Optional). The prefix is as follows:

    [EnableCors(origins: "*", headers: "*", methods: "*")]
    [RoutePrefix("api/pricehistory")]
    public class PriceHistoryController : ApiController
    {

Format String to Float

Add a function to format the float to 2 decimal places.

  private static float formatFloat(string val)
        {            
            float floatVal = float.Parse(val);
            string floatFMT = floatVal.ToString("n2");
            return float.Parse(floatFMT);
        }

All together

The Controller Action code allows for one parameter for the company or ticker symbol to be passed. The request will get a single day of data.

[HttpGet]
[Route("pricehistoryjob/{company}")]
public object PriceHistoryJob(string company)
{
    try
    {
        var today = DateTime.Today.Date.ToString("MM/dd/yyyy");                
        string apiUrl = "https://financialmodelingprep.com/api/v3/historical-price-full/" + company + " ?from=" + today + " &to=" + today;

        var request = (HttpWebRequest)WebRequest.Create(apiUrl);

        request.Method = "GET";
        request.AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip;

        var content = string.Empty;

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                using (var sr = new StreamReader(stream))
                {
                    content = sr.ReadToEnd();
                }
            }
        }

        var dataArr = JObject.Parse(content);
                
        string TickerVal = (string)dataArr["symbol"];
        var TickerSymbolValue = TickerVal.ToString().TrimStart('[').TrimEnd(']').TrimStart('"').TrimEnd('"');
        tableRecord.TickerSymbol = TickerSymbolValue.Replace('"', ' ').Trim(); ;

        var closeVal = dataArr["historical"][0]["high"];
                
        tableRecord.ClosingPrice = formatFloat(closeVal.ToString());

        var highVal = dataArr["historical"][0]["high"];
        tableRecord.High = formatFloat(highVal.ToString());

        var lowVal = dataArr["historical"][0]["low"];
        tableRecord.Low = formatFloat(lowVal.ToString());

        var dateVal = dataArr["historical"][0]["date"];
        tableRecord.ClosingDate = DateTime.Parse(dateVal.ToString());

        db.PriceHistories.Add(tableRecord);
        db.SaveChanges();
                
        return new { status = 200, msg = "Success"};
    }     
    catch (Exception e)
    {
        return new { status = 401, msg = e.InnerException};
    }
}