How to import account balance to an excel sheet

[BTC Turkiye]

I need to auto-import my account balance to my excel tracker.

So is there anyway I can import the account balance from like Binance, HitBTC etc to a specific cell in excel sheet? and it will auto update the content as well

I found few sources for this but they`re all using public APIs instead of private APIs to get the information from.

[1714948552]

1714948552

[1714948552]

1714948552

[bitmover]

Yes there is.
It's pretty easy.

You need to download power shell for Excell  and use api data from coinmarketcap (you can track data from binance exchange from their api)
I learned from this post on reddit. All detailed there. You can use his sheet or create your own. It's easy you should try.

.https://www.reddit.com/r/CryptoCurrency/comments/7m3nvy/ive_created_an_excel_crypto_portfolio_tracker/

Get the API from https://api.coinmarketcap.com/v1/ticker/bitcoin

[HCP]

You should look into "Power Query" (aka "Get & Transform" in Excel 2016): https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

It allows you to get data from a number of sources including Web API's and pass in authentication information etc... You can extract as much or as little info from the returned data and do all sorts of transformations/extractions etc...

[BTC Turkiye]

Yes there is.
It's pretty easy.

You need to download power shell for Excell  and use api data from coinmarketcap (you can track data from binance exchange from their api)
I learned from this post on reddit. All detailed there. You can use his sheet or create your own. It's easy you should try.

.https://www.reddit.com/r/CryptoCurrency/comments/7m3nvy/ive_created_an_excel_crypto_portfolio_tracker/

Get the API from https://api.coinmarketcap.com/v1/ticker/bitcoin

Thank you for trying but I dont think you read my post completely. I know there are already ways to import public data like that. I`ve looked it up everywhere and saw many examples. But what I need is importing the private data.

Like my account balance in Binance to a specific cell.
When there is a private api there is also a secret. Plus the api is not a link but 50 digit code. So this is where I`m confused.

Do you know how to import private data from an exchange to excel?

You should look into "Power Query" (aka "Get & Transform" in Excel 2016): https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

It allows you to get data from a number of sources including Web API's and pass in authentication information etc... You can extract as much or as little info from the returned data and do all sorts of transformations/extractions etc...

I have that already but couldnt figure out how and where to enter the details there. Because the API I get from my account is not a link. It`s a 50 digit code and there is a secret also.
If you know how to do it, could you please explain me?

[HCP]

I have that already but couldnt figure out how and where to enter the details there. Because the API I get from my account is not a link. It`s a 50 digit code and there is a secret also.
If you know how to do it, could you please explain me?

What you have is not the "API"... what you have is the "API Key"... you call the API via a HTTP POST call using a URL.

For instance, here is the API documentation for Poloniex... they have both Public and Private APIs...

Public example: https://poloniex.com/public?command=returnTicker

Their Private API is quite different:

All calls to the trading API are sent via HTTP POST to https://poloniex.com/tradingApi and must contain the following headers:

Key - Your API key.
Sign - The query's POST data signed by your key's "secret" according to the HMAC-SHA512 method.
Additionally, all queries must include a "nonce" POST parameter. The nonce parameter is an integer which must always be greater than the previous nonce used.

All responses from the trading API are in JSON format. In the event of an error, the response will always be of the following format:

{"error":"<error message>"}

There are several methods accepted by the trading API, each of which is specified by the "command" POST parameter:

So you need to setup a data source in Excel that creates the HTTP POST call, feeding in a "Key" parameter (ie. your 50 digit code), the "command" parameter (which are specified in the list on that page) the "nonce" value and then the "Sign" parameter which involves creating an HMAC-SHA512 of all the "POST" parameter data you've just created. Obviously, all the different exchanges use slightly different APIs but in general work on a similar process.

This guy was doing something similar with limited success: https://www.reddit.com/r/excel/comments/2ex0vh/http_post_api_request_using_power_query/
Possibly the same guy: https://www.mrexcel.com/forum/general-excel-discussion-other-questions/745134-web-api-access-sha-512-authentication.html
This link shows some of the API specific stuff: https://www.howtoexcel.org/power-query/how-to-access-a-json-api-with-power-query/

I think the issue will be generating the HMAC-512 that a lot of the exchanges require... Excel doesn't handle that stuff natively... and will require some VBA coding etc.

It might actually be easier to execute a python script to extract the data from the Exchange API and dump it out to a JSON file and then just read the JSON file into the Excel sheet, seeing as there are numerous python scripts that already exist for most of the popular exchanges.

[BTC Turkiye]

I have that already but couldnt figure out how and where to enter the details there. Because the API I get from my account is not a link. It`s a 50 digit code and there is a secret also.
If you know how to do it, could you please explain me?

What you have is not the "API"... what you have is the "API Key"... you call the API via a HTTP POST call using a URL.

For instance, here is the API documentation for Poloniex... they have both Public and Private APIs...

Public example: https://poloniex.com/public?command=returnTicker

Their Private API is quite different:

All calls to the trading API are sent via HTTP POST to https://poloniex.com/tradingApi and must contain the following headers:

Key - Your API key.
Sign - The query's POST data signed by your key's "secret" according to the HMAC-SHA512 method.
Additionally, all queries must include a "nonce" POST parameter. The nonce parameter is an integer which must always be greater than the previous nonce used.

All responses from the trading API are in JSON format. In the event of an error, the response will always be of the following format:

{"error":"<error message>"}

There are several methods accepted by the trading API, each of which is specified by the "command" POST parameter:

So you need to setup a data source in Excel that creates the HTTP POST call, feeding in a "Key" parameter (ie. your 50 digit code), the "command" parameter (which are specified in the list on that page) the "nonce" value and then the "Sign" parameter which involves creating an HMAC-SHA512 of all the "POST" parameter data you've just created. Obviously, all the different exchanges use slightly different APIs but in general work on a similar process.

This guy was doing something similar with limited success: https://www.reddit.com/r/excel/comments/2ex0vh/http_post_api_request_using_power_query/
Possibly the same guy: https://www.mrexcel.com/forum/general-excel-discussion-other-questions/745134-web-api-access-sha-512-authentication.html
This link shows some of the API specific stuff: https://www.howtoexcel.org/power-query/how-to-access-a-json-api-with-power-query/

I think the issue will be generating the HMAC-512 that a lot of the exchanges require... Excel doesn't handle that stuff natively... and will require some VBA coding etc.

It might actually be easier to execute a python script to extract the data from the Exchange API and dump it out to a JSON file and then just read the JSON file into the Excel sheet, seeing as there are numerous python scripts that already exist for most of the popular exchanges.

Thank you very much for the detailed answer. Awesome help!

I`m looking for the binance account info so I checked out their api doc information on their github page. There`s really good info there but it doesnt say anything about how to import it to excel. I shouldn`t need to write a code to import the data. Because when I need to import something from a public api, I just enter the link and it gets the data.

I`m already using power query and that`s how i connected to public apis but I just dont know how to do it for private apis. I`m not good with coding so I was trying so hard to avoid it and just import the info somewhere then see the data on the sheet.

I`m sure you already understand what I`m looking to accomplish but Just to give you an example of what I need, Check out this guys excel sheet.

https://www.youtube.com/watch?v=kPLCsKAG57g

[HCP]

I`m looking for the binance account info so I checked out their api doc information on their github page. There`s really good info there but it doesnt say anything about how to import it to excel. I shouldn`t need to write a code to import the data. Because when I need to import something from a public api, I just enter the link and it gets the data.

And that is the difference between Public and Private APIs... the Public APIs are a simple fetch of data which is identical to ALL people. The Private APIs require you to provide info (like your API key and the command you want to run) to the server and it runs the query specific to YOU and then returns the result. They are very different... and the Private API will require more effort to get working.

It's not so much that you need to write code to import the data... it's that you need to write code to tell the exchange what data you want (and prove that it is YOU that wants it)

As per the binance API Docs here: https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md#signed-trade-and-user_data-endpoint-security

USER-DATA calls requires you to create a digital signature (in this case, they use HMAC-SHA256) of the information you're sending... which WILL require that you do some coding, as that stuff just doesn't exist within Excel.

I`m already using power query and that`s how i connected to public apis but I just dont know how to do it for private apis. I`m not good with coding so I was trying so hard to avoid it and just import the info somewhere then see the data on the sheet.

Unfortunately, that just isn't going to happen if you want to use the Private APIs...

I`m sure you already understand what I`m looking to accomplish but Just to give you an example of what I need, Check out this guys excel sheet.
https://www.youtube.com/watch?v=kPLCsKAG57g

Which he has now turned into a full on "commercial" product which he is charging 0.05 ETH for... no doubt because it involved a LOT of work to get going

I very much doubt you will find a "plug and play" Excel solution... most folks who are interfacing with Exchange APIs are using Python, C#, Perl, Java or shell scripts etc, hence most of the API examples being available in these languages.

[BTC Turkiye]

Alright. One more question then;

someone says if you add the api key and the secret to the url you can access the info. Do you think it`s right? if so, can you help me on which url and where to add the keys?

[HCP]

someone says if you add the api key and the secret to the url you can access the info.

I doubt that very much... and you should NEVER use your Exchange "secret" in a URL... that is basically sending your private key in plaintext!!?!

If you do that, you're likely to get your exchange accounts hacked via the API!

This is why the exchanges run the system they do... you create the API call, then encrypt it using your "secret" (private key)... and give them the "API Key" (public key)... they can then verify that the API call was made by someone who has access to the private key... just like when you spend bitcoins!

[BTC Turkiye]

If I give only view permissions and enter it to excel only, I don`t think there`ll be a security issue with the url

I just figured out this https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23 link gives a weird error message. I feel like I`m pretty close to the solution. Do you have any advices?

[HCP]

If I give only view permissions and enter it to excel only, I don`t think there`ll be a security issue with the url

I just figured out this https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23 link gives a weird error message. I feel like I`m pretty close to the solution. Do you have any advices?

That one particular endpoint is listed as being a "GET" request, so will work by just creating a basic URL... however "account" is a USER_DATA endpoint, which is a SIGNED endpoint, which:

... require an additional parameter, signature, to be sent in the query string or request body.

So you need to calculate the appropriate HMAC-SHA256 signature for your request, as per:

- Endpoints use HMAC SHA256 signatures. The HMAC SHA256 signature is a keyed HMAC SHA256 operation. Use your secretKey as the key and totalParams as the value for the HMAC operation.
- The signature is not case sensitive.
- totalParams is defined as the query string concatenated with the request body.

And attach that to your GET request...

Code:

https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23&signature=YOUR_HMAC_SHA256_SIGNATURE_GOES_HERE&timestamp=MILLISECOND_TIMESTAMP_OF_REQUEST

Check the examples: https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md#example-1-as-a-query-string

I'm not sure you can use "key" in the query string tho... the examples all seem to indicate that it needs to be sent as:

API-keys are passed into the Rest API via the X-MBX-APIKEY header.

[BTC Turkiye]

If I give only view permissions and enter it to excel only, I don`t think there`ll be a security issue with the url

I just figured out this https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23 link gives a weird error message. I feel like I`m pretty close to the solution. Do you have any advices?

That one particular endpoint is listed as being a "GET" request, so will work by just creating a basic URL... however "account" is a USER_DATA endpoint, which is a SIGNED endpoint, which:

... require an additional parameter, signature, to be sent in the query string or request body.

So you need to calculate the appropriate HMAC-SHA256 signature for your request, as per:

- Endpoints use HMAC SHA256 signatures. The HMAC SHA256 signature is a keyed HMAC SHA256 operation. Use your secretKey as the key and totalParams as the value for the HMAC operation.
- The signature is not case sensitive.
- totalParams is defined as the query string concatenated with the request body.

And attach that to your GET request...

Code:

https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23&signature=YOUR_HMAC_SHA256_SIGNATURE_GOES_HERE&timestamp=MILLISECOND_TIMESTAMP_OF_REQUEST

Check the examples: https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md#example-1-as-a-query-string

I'm not sure you can use "key" in the query string tho... the examples all seem to indicate that it needs to be sent as:

API-keys are passed into the Rest API via the X-MBX-APIKEY header.

I don`t know how to calculate HMAC SHA256 and implement it. Can you teach me that please?
Also if you think you can give a complete example of a URL that I could just replace the keys in, that would be the best

I agree that "key" part doesn`t really work. Because I get the same error when I just write https://api.binance.com/api/v3/account

[HCP]

I won't be able to give you a URL you can just plug keys into... the "timestamp" value needs to be updated to the time the request is made/sent... it has a default timeout of 5000 milliseconds... or 5 seconds.

Also, the HMAC calculation involves using the "timestamp" parameter... so you'd need to calculate the timestamp, then calculate the HMAC and then broadcast that to Binance API within 5 seconds for it to be valid (unless you also specify RecvWindow parameter)

And no, I can't teach you how to calculate the HMAC in Excel... I've only ever used Python scripts to do this... see the links I posted earlier that show VBA code etc

[Roaid]

If I give only view permissions and enter it to excel only, I don`t think there`ll be a security issue with the url

I just figured out this https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23 link gives a weird error message. I feel like I`m pretty close to the solution. Do you have any advices?

That one particular endpoint is listed as being a "GET" request, so will work by just creating a basic URL... however "account" is a USER_DATA endpoint, which is a SIGNED endpoint, which:

... require an additional parameter, signature, to be sent in the query string or request body.

So you need to calculate the appropriate HMAC-SHA256 signature for your request, as per:

- Endpoints use HMAC SHA256 signatures. The HMAC SHA256 signature is a keyed HMAC SHA256 operation. Use your secretKey as the key and totalParams as the value for the HMAC operation.
- The signature is not case sensitive.
- totalParams is defined as the query string concatenated with the request body.

And attach that to your GET request...

Code:

https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23&signature=YOUR_HMAC_SHA256_SIGNATURE_GOES_HERE&timestamp=MILLISECOND_TIMESTAMP_OF_REQUEST

Check the examples: https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md#example-1-as-a-query-string

I'm not sure you can use "key" in the query string tho... the examples all seem to indicate that it needs to be sent as:

API-keys are passed into the Rest API via the X-MBX-APIKEY header.

I don`t know how to calculate HMAC SHA256 and implement it. Can you teach me that please?
Also if you think you can give a complete example of a URL that I could just replace the keys in, that would be the best

I agree that "key" part doesn`t really work. Because I get the same error when I just write https://api.binance.com/api/v3/account

You don't need to learn how to calculate HMAC SHA256, which is repeated inventing the wheel.
You need to find source code for calculations based on your programming language, such as python or java, and use them directly.
According to my own experience, I am a java programmer. After I found the code of HMAC SHA256, I saved it as a static class and then call it every time.

[BTC Turkiye]

Thank you very much for all the help guys

[Roaid]

If I give only view permissions and enter it to excel only, I don`t think there`ll be a security issue with the url

I just figured out this https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23 link gives a weird error message. I feel like I`m pretty close to the solution. Do you have any advices?

That one particular endpoint is listed as being a "GET" request, so will work by just creating a basic URL... however "account" is a USER_DATA endpoint, which is a SIGNED endpoint, which:

... require an additional parameter, signature, to be sent in the query string or request body.

So you need to calculate the appropriate HMAC-SHA256 signature for your request, as per:

- Endpoints use HMAC SHA256 signatures. The HMAC SHA256 signature is a keyed HMAC SHA256 operation. Use your secretKey as the key and totalParams as the value for the HMAC operation.
- The signature is not case sensitive.
- totalParams is defined as the query string concatenated with the request body.

And attach that to your GET request...

Code:

https://api.binance.com/api/v3/account?key=9Z42ubqjCJbwXAKlncofGp8LHmJThdJrBb34rwefd23&signature=YOUR_HMAC_SHA256_SIGNATURE_GOES_HERE&timestamp=MILLISECOND_TIMESTAMP_OF_REQUEST

Check the examples: https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md#example-1-as-a-query-string

I'm not sure you can use "key" in the query string tho... the examples all seem to indicate that it needs to be sent as:

API-keys are passed into the Rest API via the X-MBX-APIKEY header.

I don`t know how to calculate HMAC SHA256 and implement it. Can you teach me that please?
Also if you think you can give a complete example of a URL that I could just replace the keys in, that would be the best

I agree that "key" part doesn`t really work. Because I get the same error when I just write https://api.binance.com/api/v3/account

In java, you can call an external library directly which is

Code:

import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec

My source code like this:

Code:

Mac sha256_HMAC = Mac.getInstance("HmacSHA256");
SecretKeySpec secret_key = new SecretKeySpec(secret_Key.getBytes(), "HmacSHA256");
sha256_HMAC.init(secret_key);
String hash = Hex.encodeHexString(sha256_HMAC.doFinal(message.getBytes())).toUpperCase();
return hash;

[The best one]

Yes there is.
It's pretty easy.

You need to download power shell for Excell  and use api data from coinmarketcap (you can track data from binance exchange from their api)
I learned from this post on reddit. All detailed there. You can use his sheet or create your own. It's easy you should try.

.https://www.reddit.com/r/CryptoCurrency/comments/7m3nvy/ive_created_an_excel_crypto_portfolio_tracker/

Get the API from https://api.coinmarketcap.com/v1/ticker/bitcoin

I think If we have some account balance that we can import balance to an excel and we know that Microsoft Excel offers a wealth of data manipulation tools that may be unavailable in your current program or online database. If you need access to such tools, you may need to transfer your account balance sheet into Excel. This is typically a two-step process where you first export your account balance into a supported format, then import it into Excel. Most programs and online databases, such as Paypal, QuickBooks and online banking accounts, include tools for exporting data to the widely compatible comma-delimited format or directly into Excel format. So there are some step to Import account balance to an excel sheet :
The first   : Open or log in to your account balance sheet program and choose to import your account balance 
The second :Open Microsoft Excel and press "Ctrl-O" to invoke the Open window.
The third :Click the "All Excel Files ..." drop-down menu and select "Text Files " Doing so will allow you to
                 see the CSV file in the appropriate folder. If you exported the balance sheet into Excel format, skip
                 this step.
The forth : Select the exported file and click "Open." Because the file is in a recognized CSV or XLS format,
                  it will open automatically. I hope it can help you to import your account balance to an excel sheet.

[MaxiMan]

You should look into "Power Query" (aka "Get & Transform" in Excel 2016): https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

It allows you to get data from a number of sources including Web API's and pass in authentication information etc... You can extract as much or as little info from the returned data and do all sorts of transformations/extractions etc...

Im doing manually an earning tracker where i import my balance each 12 hours, would like to import directly data from my pool api
Im using open office calc
Hopefully my miner records a log file txt file so i just look into it and copy the balances reported. Then i can do graphs and statistics

Can I get this type of automatization on open office? Sounds interesting.

Anyone tried to import and keep updating BTC price from Localbitcoins? I fond the function to import data, but it import all whole list of sellers. Like a copy paste directly from page, I just only want to Get the First Sale Price.
About the Api on LBC, i dont get it how to use.

[HCP]

They have an "average" price ticker here: https://localbitcoins.com/bitcoinaverage/ticker-all-currencies/

You then just need to extract the data for the currency you want... like:

"USD": {"avg_12h": "9621.93", "volume_btc": "137.46286471", "avg_24h": "9670.23", "avg_1h": "9396.37", "rates": {"last": "12164.98"}, "avg_6h": "9831.19"}

About the Api on LBC, i dont get it how to use.

As per their documentation...

Before you start
The LocalBitcoins API is created for programmers who want to create applications to interface with LocalBitcoins. If you're not coming from a programming background you will probably have a difficult time understanding this guide. To help you get started with coding you can take a look at Code Academy that offers free courses in coding.

If you don't know how to work with JSON formatted data, I'd suggest you investigate ways to extract info from JSON data using whatever system you're using (For instance, try googling "Open Office JSON") first... and after you're comfortable working with JSON, you can try and retrieve the data from LBC.

[MaxiMan]

They have an "average" price ticker here: https://localbitcoins.com/bitcoinaverage/ticker-all-currencies/

You then just need to extract the data for the currency you want... like:

"USD": {"avg_12h": "9621.93", "volume_btc": "137.46286471", "avg_24h": "9670.23", "avg_1h": "9396.37", "rates": {"last": "12164.98"}, "avg_6h": "9831.19"}

Thats good site to start with i think, what I want is if there is a way to tell office (excel/calc) to read (import)  just a value, for ex  "avg_12h" so i can get a constant updated bitcoin price in the currency i want.