Oanda instruments

Posted By: kalmar

Oanda instruments - 04/15/20 21:49

Hi all,

Maybe it would be useful for someone: this Python script I was using to get all Oanda instruments available for me (including RollLong and RollShort) for creating "All Oanda Assets List". Perhaps I did something not needed and already available, but I didn't find it. Please advise if it could be done smoother.

Code
import json
import oandapyV20
import oandapyV20.endpoints.accounts as accounts
import pandas as pd

accountID = "XXX-XXX-XXXXXXX-XXX"
token = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxx"
client = oandapyV20.API(access_token=token)

r = accounts.AccountInstruments(accountID=accountID)
rv = client.request(r)
data = json.dumps(rv, indent=2)
frame = pd.json_normalize(pd.read_json(data)['instruments'])
needed_columns = ['name', 'type', 'displayName', 'minimumTradeSize','marginRate','financing.longRate','financing.shortRate']
frame[needed_columns].to_csv("Oanda_instruments.csv", index=False)
Posted By: ESR

Re: Oanda instruments - 05/03/20 17:26

kalmar, thanks for this.

Would you mind providing a step-by-step walkthrough for implementing this. because I am new to Zorro?

TIA,

~eric
Posted By: kalmar

Re: Oanda instruments - 05/04/20 15:55

Hi Eric,

I assume you have an Oanda account and Token.

As it's described in https://www.zorro-trader.com/manual/en/account.htm in order to properly backtest the strategy on a certain account you need to use settings of this account. Every asset, which is available for your account, have certain characteristics. And as Oanda provides API, you could get these characteristics by running this script in Python.

For oanda this will help as well: https://www.zorro-trader.com/manual/en/oanda.htm

However, the problem with Roll costs still remains as it is fixed for the backtest. So if your strategy holds a lot overnight it could lead to not realistic performance (too bad or too good). Maybe someone could advise how to mitigate this problem?

Thx,

Kalmar
Posted By: Morris

Re: Oanda instruments - 05/11/20 19:54

Hello kalmar,

Very useful indeed, thank you!

I took the liberty of extending your script to make it create a complete Assets.csv file, grouped by asset type, for all Oanda instruments, including the correct RollLong and RollShort values converted to the account currency. PIPCost is also calculated in account currency.

One thing to still be aware of is that Oanda appears to sometimes triple or quadruple the rollover rates even when it is not a Wednesday or Friday. I guess that could be taken care of by a script which runs a few times per week and compares the rates per instrument (manually or automatically).

Code
from collections import defaultdict
import pandas as pd

import oandapyV20
import oandapyV20.endpoints.accounts as accounts
import oandapyV20.endpoints.pricing as pricing

ACCOUNT_ID = 'XXX-XXX-XXXXXXX-XXX'
TOKEN = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxx'
ACCOUNT_CURRENCY = 'EUR'
OUTPUT_FILENAME = 'AssetsOanda.csv'

client = oandapyV20.API(access_token=TOKEN)

# Retrieve all instruments for account
r = accounts.AccountInstruments(accountID=ACCOUNT_ID)
rv = client.request(r)
data = pd.json_normalize(pd.DataFrame(rv)['instruments'])

# Retrieve pricing data for all instruments
instrument_list = list(data['name'])
r = pricing.PricingInfo(accountID=ACCOUNT_ID, params={'instruments': ','.join(map(str, instrument_list))})
rv = client.request(r)
prices = pd.json_normalize(pd.DataFrame(rv)['prices']).set_index('instrument').rename(columns={'instrument': 'Name'})
prices[['closeoutAsk', 'closeoutBid']] = prices[['closeoutAsk', 'closeoutBid']].astype(float)
prices['Price'] = prices[['closeoutAsk', 'closeoutBid']].mean(axis=1)
prices['Spread'] = prices['closeoutAsk'] - prices['closeoutBid']

# Rename columns, set instrument column as index, join with prices, and convert data types as necessary
data = data.rename(columns={'name': 'Name',
                            'minimumTradeSize': 'LotAmount'}). \
            set_index('Name'). \
            join(prices[['Price', 'Spread']])
data.index = data.index.str.replace('_','/')               # Replace '_' with '/' in line with Zorro convention
convert_columns = ['LotAmount', 'marginRate', 'financing.longRate', 'financing.shortRate']
data[convert_columns] = data[convert_columns].astype(float)
data['currency'] = data.index.map(lambda c: c[-3:])

# Add Index as type, mainly for cosmetic reasons (where name ends in a number or in 'Index')
data.loc[(data['displayName'].str[-1].str.isnumeric()) | (data['displayName'].str[-5:]=='Index'),'type'] = 'INDEX'

# Store currency rates in dict, add inverse and necessary cross currency rates to be able to convert all
# assets to account currency (also add XAG as currency for XAU/XAG)
conversion_rate = data[data['type']=='CURRENCY']['Price'].to_dict()     # All OANDA currency rates
conversion_rate.update({pair[-3:]+"/"+pair[:3]: 1/rate
                        for pair, rate in conversion_rate.items()})     # Inverse of existing currencies
conversion_rate[f"{ACCOUNT_CURRENCY}/{ACCOUNT_CURRENCY}"] = 1
assert 'USD/'+ACCOUNT_CURRENCY in conversion_rate, "Cannot calculate account currency rates"
conversion_rate['XAG/USD'] = data.loc['XAG/USD']['Price']
# Add missing currencies as cross currency via USD
conversion_rate.update({currency+'/'+ACCOUNT_CURRENCY: conversion_rate[currency+'/USD'] * conversion_rate['USD/'+ACCOUNT_CURRENCY]
                        for currency in
                            (currency for currency in data['currency']
                             if currency+'/'+ACCOUNT_CURRENCY not in conversion_rate)})

# Add additional columns, calculate PipCost based on conversion rates
data['PIP'] = 10.**data['pipLocation']
data['PIPCost'] = data['PIP'] * (data['currency']+'/'+ACCOUNT_CURRENCY).map(conversion_rate)
data['Leverage'] = (1/data['marginRate']).astype(int)
data['MarginCost'], data['Commission'] = 0, 0
data['Symbol'] = data.index

# Adjust for weekend rate inflation on Wednesdays/Fridays if necessary
# data[['financing.longRate', 'financing.shortRate']] = data[['financing.longRate', 'financing.shortRate']] / 4

# Calculate RollLong and RollShort in account currency, for the quantities used by Zorro
financing_pos_size = defaultdict(lambda: 1, {'CURRENCY': 10000})    # Everything but CURRENCY defaults to 1
data['RollLong'] = data['financing.longRate']/365 * \
                   data['Price'] * \
                   data['type'].map(financing_pos_size) * \
                  (data['currency']+'/'+ACCOUNT_CURRENCY).map(conversion_rate)
data['RollShort'] = data['financing.shortRate']/365 * \
                    data['Price'] * \
                    data['type'].map(financing_pos_size) * \
                   (data['currency']+'/'+ACCOUNT_CURRENCY).map(conversion_rate)

EXPORT_COLUMNS = ['Price', 'Spread', 'RollLong', 'RollShort',
                  'PIP', 'PIPCost', 'MarginCost', 'Leverage',
                  'LotAmount', 'Commission', 'Symbol']
DISPLAY_COLUMNS = ['type', 'displayName', 'Price', 'Spread',
                   'financing.longRate', 'financing.shortRate',
                   'RollLong', 'RollShort',
                   'PIP', 'PIPCost', 'MarginCost', 'Leverage',
                   'LotAmount', 'Commission']
data.sort_values(['type', 'Name'], inplace=True)

# Write CSV with group headers
data[EXPORT_COLUMNS].head(0).to_csv(OUTPUT_FILENAME)
max((open(OUTPUT_FILENAME, 'a').write(f"### {data_type}\n"),
          data.loc[data_group, EXPORT_COLUMNS].to_csv(OUTPUT_FILENAME, float_format='%g', header=False, mode='a'))
    for data_type, data_group in data.groupby('type').groups.items())

print(data[DISPLAY_COLUMNS].to_string())
Posted By: ESR

Re: Oanda instruments - 05/28/20 21:17

@Morris thanks for the code to create an Oanda Assets file.

When I run this script I get:

_OandaAssets compiling.......
Error in 'line 2:
'from' undeclared identifier
< from collections import defaultdict
>.

What did I miss?

~eric
Posted By: ESR

Re: Oanda instruments - 05/28/20 21:29

Thanks Kalmar,

What I meant to ask is how do you run the script.

~eric
Posted By: kalmar

Re: Oanda instruments - 05/28/20 22:16

Hey Morris,

Great stuff! I went through the code. Everything is clear.

Thank you

BTW I discovered for myself finnhub.io. They have a lot interesting stuff. Would be cool to have Zorro connection to it laugh

e.g. this snip gives you all the instruments from different forex providers they support (incl. Oanda)

Code
import fhub
hub = fhub.Session(token)
All_FX = pd.concat((hub.symbols(exch,kind='forex').assign(source = exch) 
                        for exch in hub.exchanges('forex').forex), ignore_index = True)
Posted By: DdlV

Re: Oanda instruments - 06/03/20 06:00

Hi all. Thanks to kalmar and Morris! Below is a slightly updated version with the following additions:

- Note about Account Currency to be sure the user changes it if needed
- Add timestamp to the output filename
- Note of the change needed it it's a live account
- Error handling if XAG is not valid (as for US residents)

I don't claim to be a Python programmer, so code review welcome from those who are!

Regards.

Code
from collections import defaultdict
from datetime import datetime
import pandas as pd

import oandapyV20
import oandapyV20.endpoints.accounts as accounts
import oandapyV20.endpoints.pricing as pricing

ACCOUNT_ID = 'XXX-XXX-XXXXXXX-XXX'
TOKEN = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxx'
ACCOUNT_CURRENCY = 'EUR'    # or USD or whatever
OUTPUT_FILENAME = 'AssetsOanda-' + datetime.now().strftime("%Y-%m-%d-%H-%M-%S") + '.csv'

client = oandapyV20.API(access_token=TOKEN)
# For a live account:
# client = oandapyV20.API(access_token=TOKEN,environment='live')

# Retrieve all instruments for account
r = accounts.AccountInstruments(accountID=ACCOUNT_ID)
rv = client.request(r)
data = pd.json_normalize(pd.DataFrame(rv)['instruments'])

# Retrieve pricing data for all instruments
instrument_list = list(data['name'])
r = pricing.PricingInfo(accountID=ACCOUNT_ID, params={'instruments': ','.join(map(str, instrument_list))})
rv = client.request(r)
prices = pd.json_normalize(pd.DataFrame(rv)['prices']).set_index('instrument').rename(columns={'instrument': 'Name'})
prices[['closeoutAsk', 'closeoutBid']] = prices[['closeoutAsk', 'closeoutBid']].astype(float)
prices['Price'] = prices[['closeoutAsk', 'closeoutBid']].mean(axis=1)
prices['Spread'] = prices['closeoutAsk'] - prices['closeoutBid']

# Rename columns, set instrument column as index, join with prices, and convert data types as necessary
data = data.rename(columns={'name': 'Name',
                            'minimumTradeSize': 'LotAmount'}). \
            set_index('Name'). \
            join(prices[['Price', 'Spread']])
data.index = data.index.str.replace('_','/')               # Replace '_' with '/' in line with Zorro convention
convert_columns = ['LotAmount', 'marginRate', 'financing.longRate', 'financing.shortRate']
data[convert_columns] = data[convert_columns].astype(float)
data['currency'] = data.index.map(lambda c: c[-3:])

# Add Index as type, mainly for cosmetic reasons (where name ends in a number or in 'Index')
data.loc[(data['displayName'].str[-1].str.isnumeric()) | (data['displayName'].str[-5:]=='Index'),'type'] = 'INDEX'

# Store currency rates in dict, add inverse and necessary cross currency rates to be able to convert all
# assets to account currency (also add XAG as currency for XAU/XAG)
conversion_rate = data[data['type']=='CURRENCY']['Price'].to_dict()     # All OANDA currency rates
conversion_rate.update({pair[-3:]+"/"+pair[:3]: 1/rate
                        for pair, rate in conversion_rate.items()})     # Inverse of existing currencies
conversion_rate[f"{ACCOUNT_CURRENCY}/{ACCOUNT_CURRENCY}"] = 1
assert 'USD/'+ACCOUNT_CURRENCY in conversion_rate, "Cannot calculate account currency rates"

# Except, US types won't have XAG...
try:
    conversion_rate['XAG/USD'] = data.loc['XAG/USD']['Price']
except KeyError:
    print("No XAG/USD - is this a US account?!")

# Add missing currencies as cross currency via USD
conversion_rate.update({currency+'/'+ACCOUNT_CURRENCY: conversion_rate[currency+'/USD'] * conversion_rate['USD/'+ACCOUNT_CURRENCY]
                        for currency in
                            (currency for currency in data['currency']
                             if currency+'/'+ACCOUNT_CURRENCY not in conversion_rate)})

# Add additional columns, calculate PipCost based on conversion rates
data['PIP'] = 10.**data['pipLocation']
data['PIPCost'] = data['PIP'] * (data['currency']+'/'+ACCOUNT_CURRENCY).map(conversion_rate)
data['Leverage'] = (1/data['marginRate']).astype(int)
data['MarginCost'], data['Commission'] = 0, 0
data['Symbol'] = data.index

# Adjust for weekend rate inflation on Wednesdays/Fridays if necessary
# data[['financing.longRate', 'financing.shortRate']] = data[['financing.longRate', 'financing.shortRate']] / 4

# Calculate RollLong and RollShort in account currency, for the quantities used by Zorro
financing_pos_size = defaultdict(lambda: 1, {'CURRENCY': 10000})    # Everything but CURRENCY defaults to 1
data['RollLong'] = data['financing.longRate']/365 * \
                   data['Price'] * \
                   data['type'].map(financing_pos_size) * \
                  (data['currency']+'/'+ACCOUNT_CURRENCY).map(conversion_rate)
data['RollShort'] = data['financing.shortRate']/365 * \
                    data['Price'] * \
                    data['type'].map(financing_pos_size) * \
                   (data['currency']+'/'+ACCOUNT_CURRENCY).map(conversion_rate)

EXPORT_COLUMNS = ['Price', 'Spread', 'RollLong', 'RollShort',
                  'PIP', 'PIPCost', 'MarginCost', 'Leverage',
                  'LotAmount', 'Commission', 'Symbol']
DISPLAY_COLUMNS = ['type', 'displayName', 'Price', 'Spread',
                   'financing.longRate', 'financing.shortRate',
                   'RollLong', 'RollShort',
                   'PIP', 'PIPCost', 'MarginCost', 'Leverage',
                   'LotAmount', 'Commission']
data.sort_values(['type', 'Name'], inplace=True)

# Write CSV with group headers
data[EXPORT_COLUMNS].head(0).to_csv(OUTPUT_FILENAME)
max((open(OUTPUT_FILENAME, 'a').write(f"### {data_type}\n"),
          data.loc[data_group, EXPORT_COLUMNS].to_csv(OUTPUT_FILENAME, float_format='%g', header=False, mode='a'))
    for data_type, data_group in data.groupby('type').groups.items())

print(data[DISPLAY_COLUMNS].to_string())
Posted By: Morris

Re: Oanda instruments - 06/03/20 21:22

Hi DdlV,

Very useful -- thanks! (By the way, in order to check for the existence of XAG/USD, instead of try/except, you could use: if 'XAG/USD' in data.index ... -- but I realize this is not a Python forum...)

And kalmar, thanks for the pointer to finnhub.io. I look forward to checking it out!

@Eric: The code above is a Python script, so it won't run in Zorro. But it makes it easier to create the Zorro assets file for Oanda. To run it, just download and install Python and a few dependent packages.
Posted By: DdlV

Re: Oanda instruments - 06/04/20 00:09

Thanks, Morris, for the Python mini-lesson!

I guess one could also use try/except for the initial account access to print a nice error message rather than just having an error dump if it's a live account... Wouldn't want the code to just fix itself and forge ahead on a live account, though - bad precedent!

Regards.
Posted By: YG8

Re: Oanda instruments - 06/04/20 06:24

I am a total novice when it comes to this so my apologies for the stupid question

First of all I installed python version 3.6.8 32-bit since that is what the Zorro manual says.

When I try to run the code in jupyter notebook I get an error message

---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
<ipython-input-1-8f5335d6a9a7> in <module>
2 import pandas as pd
3
----> 4 import oandapyV20
5 import oandapyV20.endpoints.accounts as accounts
6 import oandapyV20.endpoints.pricing as pricing

ModuleNotFoundError: No module named 'oandapyV20'


When I try to run the code in IDLE python shell i gett the following error message

SyntaxError: multiple statements found while compiling a single statement

I did use the pip option and installed both the oandapyV20 module and pandas

Where could I successfully run the code? (I have an Oanda account and did enter the account details)
Posted By: Morris

Re: Oanda instruments - 06/05/20 10:24

Hi YG8,

I'm not sure this will run in Jupyter or in the shell (depending on how it is set up).

> I did use the pip option and installed both the oandapyV20 module and pandas

Good, that's an important step!

> SyntaxError: multiple statements found while compiling a single statement

That is probably because the shell usually just takes one statement at a time. It is not meant to execute scripts.

=> I would suggest to just run the script locally: python <scriptname>. Or, if your python is installed accordingly, just run the <script.py>, and it will be interpreted by python.

Good luck!
Posted By: Grat

Re: Oanda instruments - 06/09/20 10:17

If anybody have time. This is script for reading information in MT4.

symbols.raw ( located in ".\history\default" )
fce return pandas dataset:


Code
def read_symbol(filepath):
    with open(filepath, 'rb') as f:
        
        dtype = [('symbol', 'S12'),   #OK
                 ('desc', 'S64'),     # 12:76 OK
                 ('alt', 'S12'),      # 76:88 OK
                 ('base', 'S12'),     # 88:100 OK
                 ('group', 'i4'),     # 100:104 OK
                 ('digits', 'i4'),    # 104:108
                 ('trademode', 'i4'), # 108:112
                 ('bcolor', 'i4'),    # 112:116 OK
                 ('id', 'i4'),        # 116:120
                 ('u1', 'a1508'),     # 120:1628 OK
                 ('u2', 'u4'),        # 1628:1632
                 ('u3', 'a8'),        # 1632:1640
                 ('u4', 'f8'),        # 1640:1648
                 ('u5', 'a12'),       # 1648:1660
                 ('spread', 'i4'),    # 1660:1664
                 ('u6', 'a16'),       # 1664:1680
                 ('swLong', 'f8'),    # 1680:1688
                 ('swShort', 'f8'),   # 1688:1696
                 ('swap3', 'u4'),     # 1696:1700
                 ('u8', 'i4'),        # 1700:1704
                 ('conSize', 'f8'),   # 1704:1712
                 ('u9', 'a16'),       # 1712:1728
                 ('stopLevel', 'u4'), # 1728:1732
                 ('u10', 'a12'),      # 1732:1744
                 ('marginInit', 'f8'),# 1744:1752
                 ('marginMant', 'f8'),  # 1752:1760
                 ('marginHedg', 'f8'),  # 1760:1768 
                 ('marginDiv', 'f8'),   # 1768:1776
                 ('pointPerUnit', 'f8'), # 1776:1784
                 ('u11', 'a24'),         # 1784:1808
                 ('marginCurr', 'S12'),  # 1808:1820
                 ('u12', 'S104'),        # 1820:1924 
                 ('u13', 'i4'),          # 1924:1928
                 ('u14', 'f8')]          # 1928:1936
                
        df = pd.DataFrame(np.frombuffer(f.read(), dtype=dtype).astype(dtype))
        dropnames=[]
        for x in range(1,15):
            dropnames.append('u'+str(x))   
        del dropnames[6:7]
        dropnames.append("desc")
        dropnames.append("alt")
        dropnames.append("marginCurr")        
        df=df.drop(dropnames, axis=1)
        return df


P.S. can be also in Zorro, but python I know better

Attached picture Snímek obrazovky 2020-06-09 v 15.16.28.png
Posted By: seerwright

Re: Oanda instruments - 05/01/22 23:40

Thanks, Grat!
© 2024 lite-C Forums