Back to Community
Global equities price data

G'day, desperately need some help.

a broad overview of what I am trying to achieve:
1. get pricing data for a hand picked list of ETFs that are listed on the ASX
2. Spearman's rank correlation coefficient matrix of said ETF's

for each ETF, I am trying to add a pd series containing closing price data (with date time indexes) as columns to a pd dataframe.
I am getting NaNs for the second column (probably because the indexes from the first contain two levels - date time and zipline equity).

Any help would be much appreciated.

Loading notebook preview...
2 responses

Great questions!

The biggest difference (challenge) when working with non-US securities is the current need to select securities by SID and not symbol. One typically first gets a pipeline of all securities, then searches by symbol to find the SID.

So, first 1. get pricing data for all securities. This can be done in the pipeline. Prices can be returned as part of the pipeline. No need for a second step to add prices.

Next, 2a. select a subset of securities by symbol. This can be done by adding a column of symbols, then using the query method to select only certain symbols. There are of course other ways to do this too.

Finally, 2b. get Spearman's rank correlation coefficient matrix of securities This can be easily done with a single pandas corr method. However, this method expects the securities to be in columns. Use the unstack method first to swap the securities index into columns.

That's it!

There are a couple of issues with this however. One doesn't typically want to find correlations of prices but rather returns. See this post for more insight and a notebook which highlights the pitfall of looking for correlations in prices ( The other issue is that a pipeline returns prices which are adjusted as of each pipeline date. If there were a stock split, the timeseries pipeline data would look like a large price change. Probably not good. Returns however, won't be affected by splits. Use returns instead of prices and it fixes both these issues.

See attached notebook. Also an easy way to visualize the output as a heatmap using seaborn.

Loading notebook preview...

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by Quantopian. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. No information contained herein should be regarded as a suggestion to engage in or refrain from any investment-related course of action as none of Quantopian nor any of its affiliates is undertaking to provide investment advice, act as an adviser to any plan or entity subject to the Employee Retirement Income Security Act of 1974, as amended, individual retirement account or individual retirement annuity, or give advice in a fiduciary capacity with respect to the materials presented herein. If you are an individual retirement or other investor, contact your financial advisor or other fiduciary unrelated to Quantopian about whether any given investment idea, strategy, product or service described herein may be appropriate for your circumstances. All investments involve risk, including loss of principal. Quantopian makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances.

Awesome work Dan. Thanks for getting back to me so quickly.
Also, that is a great point, I will be switching to returns.