reference matrix name in a formula
If ColumnA contains Matrix names, how to reference the matrix with that name in ColumnB in a formula?
ColumnB = ColumnA::rows 1
Similar to using @ for Category names
Problem: for now I have 10 matrices to combine some of their elements into one matrix
Hi Bristly, I think I found a solution for you. As you see on the screenshot I used indirect function to make a matrix name variable. You do not need to change this formula when you add a new ticker, it works automatically when you add a new ticker item. Hope I could help you.
In fact, all I need is indirect(@F&”::Column 14″) to get the one item I need out each @F matrix. Without indirect() the variable matrix name is interpreted as text.
I am glad that I could help you. Good luck with your Quantrix models!
Hi Bristly, it is a good question. I understand the pain of doing it one-by-one. The best way would be to find a single source or a single link to all of your tickers. But if you do no want to pay for such service, I think you can use a script. This script can update your data links, loop through all matrices, get necessary data and paste it into summary matrix. Quantrix formula cannot loop through all matrices like that.
I had a multi-ticker method but they started charging $. Guess I’ll live with as many formulas as DataLinks–that is working now. There are no more than 9 tickers and I check it just once a week or less.
Just seems odd to me that a Matrix name cannot be a variable like a @Category
Hi all,
Here is a solution to the problem with using the ConcatLists() function and $MatrixName:
*
*
Good luck
Hi all!
Here is a solution to the problem without using the Selectlist() function:
*
*
Good luck
“combine them all into one matrix…”
That is essentially what I’m trying to do. I grab market data for 10 tickers using a DataLink for each ticker so each each ticker makes it’s own single-row multi-column matrix named for the ticker symbol (best free option I have found for EOD data, unable to use single DataLink for multiple tickers).
Without seeing how you’re getting the EOD data, I’d suggest:
1) Download ticker data to .csv or Excel file
2) Get all the tickers onto a single sheet (use PowerQuery in Excel if necessary) and format as a flat/2d table (Ticker symbol, date, value, etc. as columns across the top)
3) Use Data Import (not DataLink) to bring in all the tickers & data to a single matrix to operate on
If you’re still stuck and want to take a look, feel free to email me lweber@quantrix.com
That sounds like a lot of manual work. Trying to automate this inside Quantrix.
DataLink gets one ticker’s data and gives me a row of values, I only want the one number in the 14th column but I bring all the columns in. The row does not contain the ticker so I use the DataLink to create a matrix named after the ticker. That gives me 10 matrices identical in structure with categories Rows and Data but each with a unique name. I’d like to get that data, or just the 14th column, into one matrix associated with the ticker symbol.
Seems like there should be a generic way to collate data from several matrices. Right now I just have one formula for each ticker in a unifying matrix but would rather have just one for when the tickers change.
Hello Bristly, we would advise not making matrix selection part of your model’s structural design.
If you have multiple matrices with similar structure, sometimes you can combine them all into one matrix with a category shared across the former matrix names. I’d have to learn more about your model and what you are attempting to do. Sometimes your formulas may have to be a little less fancy than you might be hoping to achieve.
It’s also possible that you could join a few matrices/categories together via Data Import from Matrix, but this may not be what you need.
Lyndsey
This is exactly what I was looking for. It works. Thank you.
My difficulty was not the syntax of the matrix name call as I thought but how it was being used; indirect() solves the actual problem.