• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

calling excel experts

Bodhi Dharma Zen

Advaitin
Bodhi Dharma Zen
Joined
Nov 25, 2004
Messages
3,926
I'm getting some data from a trading software, and I want to use excel to make a composite of all the data together. The program in question (Tradestation) gives me a sheet with various statistical information for every stock using my algorithms, and what I want is to be able to "join" the data of every stock in order to analyze the behavior of the entire portfolio.

There is a software that does exactly this, "Portfolio Maestro" from RINA, but I believe excel can do the same thing without the cost...

If you need more information please don't hesitate to ask.
 
Definitely need more information. Are you saying you have a separate file for each stock or that there is a separate sheet within a single workbook or are all stocks on a single sheet?
 
Ok, kind of hard to explain... I have a separate file for each stock yes, it is an XML file that opens with Internet Explorer, I can access raw data like "trades list", a "performance summary" with various kind of statistical data and even graphs.

What I'm attempting to do right now is simple cut and paste the "performance summary" and put it in excel. Now, let's say that I have the "total net profit", "percent profitable" and "ratio avg win/loss" (among other data) and I want to put them together, this is, stock 1 plus stock 2 plus stock 3 and so on, in order to have a portfolio analysis instead of a stock by stock analysis.
 
Do you have access to SAS or any SAS product? What you're attempting to do sounds like simple concatenation, which would be really easy in SAS.
 
Would this thread be better off in the computer section?

I really have no idea--perhaps other members would know.
 
As long as the stock names are consistent across all data sources, copy them all into separate sheets in the same workbook then use the vlookup function on a summary sheet to join each instance on all workbooks. Make sure the stock names are sorted in ascending order in each worksheet.

Embed your vlookup's in an If statement for error control and to show a default value if a particular stock has no match to a particular data source.
 
What version of Excel? I believe 2007 and forward can open XML files directly.

Another approach, assuming the same data ends up in the same cell for each stock, would be to use the INDIRECT function to gather information from various sheets / workbooks.

Here is a far more detailed explanation than I could give:

Excel INDIRECT function

So column A has the names of sheets, column B could have something like
=INDIRECT(A1+"!C23")
to give you the value of cell C23 for the named worksheet.
 
A Laughing Baby said:
Do you have access to SAS or any SAS product? What you're attempting to do sounds like simple concatenation, which would be really easy in SAS.
Click to expand...

Nope, and yes, that's what I want to do, in order to have a portfolio analysis instead of stock by stock analysis.

SonOfLaertes said:
As long as the stock names are consistent across all data sources, copy them all into separate sheets in the same workbook then use the vlookup function on a summary sheet to join each instance on all workbooks. Make sure the stock names are sorted in ascending order in each worksheet.

Embed your vlookup's in an If statement for error control and to show a default value if a particular stock has no match to a particular data source.
Click to expand...

Thanks, they are consistent, I will give it a try right now!
 
Zax63 said:
What version of Excel? I believe 2007 and forward can open XML files directly.

Another approach, assuming the same data ends up in the same cell for each stock, would be to use the INDIRECT function to gather information from various sheets / workbooks.

Here is a far more detailed explanation than I could give:

Excel INDIRECT function

So column A has the names of sheets, column B could have something like
=INDIRECT(A1+"!C23")
to give you the value of cell C23 for the named worksheet.
Click to expand...

Actually I'm using OpenOffice Calc, which is pretty similar. Will look in to this thanks.
 
Last edited:
Ok I have looked at "Indirect" and "Vlookup" functions and I still don't get a clue! :(

I wish is was as simple as having a wizard, which columns you want? they will be averaged with? and gave me the results!
 
Bodhi Dharma Zen said:
Ok, kind of hard to explain... I have a separate file for each stock yes, it is an XML file that opens with Internet Explorer, I can access raw data like "trades list", a "performance summary" with various kind of statistical data and even graphs.
Click to expand...
Depending on how many files and how much data you need out of them (and how much experience you have with this sort of thing), perhaps an XML parser would provide a "quick fix". Extract the data from the files and drop it into a spreadsheet.
 
Alternatively, pay me, and I'll concatenate them for you and send them back to you in excel format ;)
 
jaydeehess said:
Well, the other choice is to create your own excel spreadsheet to do what ever you want. Drawback would be that you will have to manually enter all the data.
Click to expand...

I might end doing this for the moment, because there are A LOT of data I want to include in my analysis. Thanks for the suggestion

LordoftheLeftHand said:
Depending on how many files and how much data you need out of them (and how much experience you have with this sort of thing), perhaps an XML parser would provide a "quick fix". Extract the data from the files and drop it into a spreadsheet.
Click to expand...

Sounds interesting, I did a quick search in XML parsers but apparently you need to be a programmer or something to make some of out of them

A Laughing Baby said:
Alternatively, pay me, and I'll concatenate them for you and send them back to you in excel format ;)
Click to expand...

hehe thanks for the offer... what would you use to do it?
 
You could easily automate it in Excel using VBA

Of course, this means programming required :)
 
Concatenation in Excel is simple =A1&A2&A3 will combine all three cells.

I used to know a lot about Excel. Not sure what you want doing. Tell me and I will tell you how to do it in Excel, probably without using VBA.
 
if you can post a sample of the data (with personal information masked), then I'm sure I can show you how to accomplish what you're trying to do
 
You must log in or register to reply here.

Back
Top Bottom