AnsweredAssumed Answered

API Architecture

Question asked by chuckburr on May 17, 2018
Latest reply on May 17, 2018 by Jason Wood

Like • Show 0 Likes0
Comment • 11

I need to access historical trading price data. Scale is 1500 securities per day x 365 days = 547,500 records per year x 4 years history = about 2 million records. Its a very skinny file be long one.

I need to use the data in calculations so it cannot reside externally, so I need a download of trade history data from 2014 to now, then add yesterday's trade history for 1500 securities after close of business each day.

How to best architecture from FileMaker?

Can we call an API to pull the data directly into FileMaker instead of the API calling it to a PostgreSQL table then calling into FileMaker?

Jason Wood May 17, 2018 2:14 PM

Correct Answer

FileMaker can integrate directly with the API.

The main script step you will use is "Insert From URL" and its new (as of version 16) cURL capabilities.

You will also need to learn the JSON functions to parse the responses.

Try searching for "FileMaker cURL Tutorial" or "FileMaker APIs Tutorial" or similar to get started. Don't get confused by the "Data API", which is something different (where the FileMaker Server is the host of the API).

See the reply in context

No one else had this question

Outcomes

Jason Wood May 17, 2018 1:28 PM

Sounds like right now you have the data going into a PostgreSQL database, then importing info FileMaker, and you're asking if you can go direct to FileMaker from the source.

So... what is the source?

Actions

Jason Wood @ chuckburr on May 17, 2018 1:37 PM

What I should have said is - how do you get it into the SQL database?

Actions

chuckburr @ Jason Wood on May 17, 2018 1:40 PM

Call Published API. Several of these market cap sites have documented APIs

Actions

Jason Wood @ chuckburr on May 17, 2018 1:47 PM

Just looked at the first one. There is a JSON REST API. You can integrate that using Insert From URL, and then loop through the response JSON to add records to FileMaker (if that's what you want to do).

But if you already have all this data in an SQL database... why don't you just import from that... then use the APIs just to add the new information each day.

I don't feel like I have a complete picture of what you do now and the reasons you want to change. That might make it easier to give advice.

Actions

chuckburr @ Jason Wood on May 17, 2018 1:59 PM

I believe I only have access to these website's data via their published APIs.

I need to call on big historical flush to start, "give me everything you have since 2014" to start y FileMaker DB.

Then, each evening, "give me yesterday's trades" to add to my FileMaker database.

I can ask them is I can get SQL access, its not my data, I would imagine they are pretty proprietary about their data, lol.

I am having someone write the APIs I need to get the data from one of these sites. My question really is, once they do that, hope do I call best that data into FileMaker?

How can I interoperate with Node code natively? can you interop with a JSON API?

Actions

Jason Wood @ chuckburr on May 17, 2018 2:02 PM

I thought you already had a SQL database with all the history in it...

The APIs will each likely have limits for the number of records returned in a request, and limits for the number of requests (API calls) you can make in a given time period. You'll just have to check the documentation for each, and then you can write a schedule to make these calls.

Say all your sources have hourly limits. You could setup a script to run every hour as a server scheduled script, and you can design it to make the maximum number of calls to each of those sources.

If the maximum number of calls might take more than an hour for your server to execute, you'll have to account for that in your design.

Actions

chuckburr @ Jason Wood on May 17, 2018 2:06 PM

No I don't, third parties have the data I need. Good feedback above.

How do I interact with the API's above in FileMaker? Does the API dev have to create a postgreSQL database that I query from FileMaker? This is the part I am asking about. How best to architecture this.

Actions

Jason Wood @ chuckburr on May 17, 2018 2:14 PM

FileMaker can integrate directly with the API.

The main script step you will use is "Insert From URL" and its new (as of version 16) cURL capabilities.

You will also need to learn the JSON functions to parse the responses.

2 people found this helpful

Actions

chuckburr @ Jason Wood on May 17, 2018 2:18 PM

Do you mean this tutorial?

filemaker curl tutorial - Bing video

Actions

Jason Wood @ chuckburr on May 17, 2018 2:19 PM

I'm not familiar with any particular one but that looks like the perfect place to start.

Actions

11 Replies

Jason Wood May 17, 2018 1:28 PM
Mark Correct
Correct Answer
Sounds like right now you have the data going into a PostgreSQL database, then importing info FileMaker, and you're asking if you can go direct to FileMaker from the source.

So... what is the source?
Like • Show 0 Likes0
Actions
chuckburr @ Jason Wood on May 17, 2018 1:31 PM
Mark Correct
Correct Answer
One of these

https://coinmarketcap.com/gainers-losers/
https://cryptocoincharts.info/
https://www.tradingview.com/markets/cryptocurrencies/prices-all/
https://www.cryptocompare.com/coins/list/USD/1
https://onchainfx.com/
https://www.worldcoinindex.com/
http://coincap.io/
https://bitscreener.com/
https://www.coingecko.com/en
Like • Show 0 Likes0
Actions
Jason Wood @ chuckburr on May 17, 2018 1:37 PM
Mark Correct
Correct Answer
What I should have said is - how do you get it into the SQL database?
Like • Show 0 Likes0
Actions
chuckburr @ Jason Wood on May 17, 2018 1:40 PM
Mark Correct
Correct Answer
Call Published API. Several of these market cap sites have documented APIs
Like • Show 0 Likes0
Actions
Jason Wood @ chuckburr on May 17, 2018 1:47 PM
Mark Correct
Correct Answer
Just looked at the first one. There is a JSON REST API. You can integrate that using Insert From URL, and then loop through the response JSON to add records to FileMaker (if that's what you want to do).

But if you already have all this data in an SQL database... why don't you just import from that... then use the APIs just to add the new information each day.

I don't feel like I have a complete picture of what you do now and the reasons you want to change. That might make it easier to give advice.
Like • Show 0 Likes0
Actions
chuckburr @ Jason Wood on May 17, 2018 1:59 PM
Mark Correct
Correct Answer
I believe I only have access to these website's data via their published APIs.
I need to call on big historical flush to start, "give me everything you have since 2014" to start y FileMaker DB.
Then, each evening, "give me yesterday's trades" to add to my FileMaker database.

I can ask them is I can get SQL access, its not my data, I would imagine they are pretty proprietary about their data, lol.

I am having someone write the APIs I need to get the data from one of these sites. My question really is, once they do that, hope do I call best that data into FileMaker?

How can I interoperate with Node code natively? can you interop with a JSON API?
Like • Show 0 Likes0
Actions
Jason Wood @ chuckburr on May 17, 2018 2:02 PM
Mark Correct
Correct Answer
I thought you already had a SQL database with all the history in it...

The APIs will each likely have limits for the number of records returned in a request, and limits for the number of requests (API calls) you can make in a given time period. You'll just have to check the documentation for each, and then you can write a schedule to make these calls.

Say all your sources have hourly limits. You could setup a script to run every hour as a server scheduled script, and you can design it to make the maximum number of calls to each of those sources.

If the maximum number of calls might take more than an hour for your server to execute, you'll have to account for that in your design.
Like • Show 0 Likes0
Actions
chuckburr @ Jason Wood on May 17, 2018 2:06 PM
Mark Correct
Correct Answer
No I don't, third parties have the data I need. Good feedback above.

How do I interact with the API's above in FileMaker? Does the API dev have to create a postgreSQL database that I query from FileMaker? This is the part I am asking about. How best to architecture this.
Like • Show 0 Likes0
Actions
Jason Wood @ chuckburr on May 17, 2018 2:14 PM
Unmark Correct
Correct Answer
FileMaker can integrate directly with the API.

The main script step you will use is "Insert From URL" and its new (as of version 16) cURL capabilities.

You will also need to learn the JSON functions to parse the responses.

Try searching for "FileMaker cURL Tutorial" or "FileMaker APIs Tutorial" or similar to get started. Don't get confused by the "Data API", which is something different (where the FileMaker Server is the host of the API).
2 people found this helpful
Like • Show 1 Like1
Actions
chuckburr @ Jason Wood on May 17, 2018 2:18 PM
Mark Correct
Correct Answer
Do you mean this tutorial?

filemaker curl tutorial - Bing video
Like • Show 0 Likes0
Actions
Jason Wood @ chuckburr on May 17, 2018 2:19 PM
Mark Correct
Correct Answer
I'm not familiar with any particular one but that looks like the perfect place to start.
Like • Show 0 Likes0
Actions

API Architecture

Outcomes

Related Content

Recommended Content