Ascend Developer Hub

Financial Data with API's

Walkthrough of the Financial Data with API's sample dataflow

πŸ“˜

Request a Trial!

The sample dataflow mentioned in this document is only available to trial users. You can request a trial of the Ascend platform here!

Highlighted Features

API Read Connectors: Reading data from any REST APIs into Ascend with Python.
SQL + PySpark: Modular transforms in both languages with complete lineage and dependency management

Introduction

Finance and investment companies are interested in macro economic indicators in order to visualize and understand the overall health of the economy. These indicators usually need to be aggregated from a wide range of public and private data sources. Rather than building custom data pipelines with individual scheduling that rarely exist on the same instance or workspace, Ascend allows these companies to create a single source of truth for all macroeconomic indicators in an easy-to-use environment.

Many modern data platforms have custom logic for pulling in data from hundreds if not thousands of different data sources. However, there are usually very few touch points or shared logic between these ingestion workflows. With Ascend, data ingestion is limited to minimal amounts of code because common logic in terms of scheduling, polling, parsing, and ingestion have been abstracted away from the user. Moreover, our powerful transforms allow for joining of data and usage of data at any point in the dataflow.

🚧

Disclaimer

This sample dataflow serves to showcase Ascend.io's platform and is not intended to serve as a recommendation to buy or sell any security. The dataflow is not a research report of any kind and does not serve as the basis for any investment decision(s).

Why Ascend?

  • Full Automation
    Ascend provides full automation that requires no code for complicated scheduling, alerting, and maintenance. Furthermore, Ascend's automation framework can accomplish retries when there are transient errors returned from these public API endpoints.
  • Zero Infrastructure
    Ascend fullya manages all infrastructure so our users don't have to manage any infrastructures such as servers and storages.
  • Cross Platform
    Ascend brings together data from different cloud, different APIs and different data systems together for user to explore with either SQL or PySpark. Furthermore, the Ascend backend can also be hosted on any major public cloud such as AWS, GCP, and Azure.

Dataflow Walkthrough

Read Connectors

This dataflow has five read connectors from two API's: the FRED API and the AlphaVantage API. Both of these API's have public API keys that all users are encouraged to generate for themselves and use when exporting and importing this sample dataflow. The FRED API exposes financial metrics related to the Federal Reserve and other aggregated public financial data resources. The AlphaVantage API contains real time and historical data for many equities including stocks, forex, and cryptocurrencies.

Read ConnectorsDescription
1-Year Treasury RatePulling from the FRED API's DGS1 Series, this read connector ingests two columns that associate dates with the percentage value of the 1-Year Treasury Constant Maturity Rate in the US for the past several decades. This connector updates every day with a new entry.
2-Year Treasury RatePulling from the FRED API's DGS2 Series, this read connector ingests two columns that associate dates with the percentage value of the 2-Year Treasury Constant Maturity Rate in the US for the past several decades. This connector updates every day with a new entry.
10-Year Treasury RatePulling from the FRED API's DGS10 Series, this read connector ingests two columns that associate dates with the percentage value of the 10-Year Treasury Constant Maturity Rate in the US for the past several decades. This connector updates every day with a new entry.
AlphaVantage IntradayAlphaVantage's Intraday API endpoint allows us to query for the relevant metrics for stock(s) at a minute-level granularity in a single trading day time window. This connector updates at a 10-minute interval.
AlphaVantage DailyAlphaVantage's Daily API endpoint allows us to query for the relevant metrics for stock(s) at a daily granularity in a two decade time window. This connector updates daily.

Notes

  • The AlphaVantage API rate limits at ~5 requests/minute, so intermittent errors can actually be fixed with our reset errors functionality, which can be found in the "Errors" tab on the side panel for each connector.
  • When exporting this dataflow and importing it into your own data service, we encourage you to obtain your own API Keys at the following locations. Not only will this allow you to control your API usage, but also will give you the opportunity to scale up the dataflow ingestion if you choose to increase the number of stocks, refresh frequency, or more.
    FRED API Key
    AlphaVantage API Key

Yield Curve Transforms and Data Feed

Using ingested data from the FRED API, we want to derive some additional economic indicators. For this sample dataflow, our goal will be to calculate the popular (and potentially overhyped) yield curve.

Reference(s):
What is the Yield Curve?

Transforms and Data FeedsDescription
Treasury Rates - UnionSince all of our treasury rate read connectors share the same date columns, we can union them all together to view the three separate rates for each day at the same place.
Yield CurveWe choose to calculate our yield curve as the 10-year rate minus the 2-year rate. With a tiny amount of SQL, the yield curve is generated.
Yield Curve - Daily DeltaFinally, knowing the rate of change of the yield curve may also help with future transforms. We use PySpark to add a column called prev_yield_val using PySpark's lag function. We can then perform some simple subtraction to figure out the daily delta for the yield curve.
Yield Curve [Data Feed]We have published the Yield Curve transform so that you can easily bring in the Yield Curve data into your own data service!

Golden Cross Transforms

Using ingested data from AlphaVantage's Daily endpoint, we want to derive some common technical trading indicator(s). We will focus on the golden cross and other simple moving average crosses.

Reference(s):
What is a Golden Cross?

TransformsDescription
200-day SMAWith PySpark's Window and lag, we can build a transform to compute the simple moving average for our upstream dataset.
50-day SMAWith PySpark's Window and lag, we can build a transform to compute the simple moving average for our upstream dataset.
10-day SMAWith PySpark's Window and lag, we can build a transform to compute the simple moving average for our upstream dataset.
3-day SMAWith PySpark's Window and lag, we can build a transform to compute the simple moving average for our upstream dataset.
50 vs 200 SMA trendTo simplify our cross calculation, we now calculate a binary indicator (-1 or 1), which tells us the difference in the moving averages every day. In this case, we are calculating 50-day SMA - 200-day SMA.

This transform is written in SQL because the logic is rather simple, but the same result can also be just as easily accomplished in PySpark.
3 vs 10 SMA trendTo simplify our cross calculation, we now calculate a binary indicator (-1 or 1), which tells us the difference in the moving averages every day. In this case, we are calculating 3-day SMA - 10-day SMA.

This transform is written in SQL because the logic is rather simple, but the same result can also be just as easily accomplished in PySpark.
50 vs 200 SMA Cross DetectorFinally, we use PySpark to translate the change in our upstream binary indicator into the actual detection of a cross. We reduce the number of records to only display important dates where a cross actually occurs.
3 vs 10 SMA Cross DetectorFinally, we use PySpark to translate the change in our upstream binary indicator into the actual detection of a cross. We reduce the number of records to only display important dates where a cross actually occurs.

Next Steps

Now that you have been introduced to this dataflow, we encourage you to continue to build on it and play around with its content in your own environment. You can easily bring this sample dataflow into your own data service with our Importing & Exporting (requires account access) feature!

Here are some ideas of things that you can do to get started with your own copy of Financial Data with API's

  • Adjust the custom read connectors to read a different stock or number of stocks!
  • Export one of the FRED read connectors and build your own read connector for another FRED series like UNRATE!
  • Try computing the Yield curve between the 1-Year and 10-Year treasury rates.
  • Build a Pyspark Transform off of AlphaVantage Daily (requires account access) that computes an exponential moving average of stock price.
  • Build a Pyspark Transform that uses the final Yield Curve transform and a Moving Average transform as inputs. You can then try to analyze the correlation between the two transforms using PySpark's correlation function!

Questions or Suggestions

Feel free to send an email to [email protected] if you have any additional questions, comments, or suggestions for this dataflow!

Updated 8 months ago

Financial Data with API's


Walkthrough of the Financial Data with API's sample dataflow

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.