I've managed to put together something reasonable in Excel so that I have a live snapshot of open positions, risk and margins etc (linked to eSignal data manager) , all basic stuff so far but I've come to a bit of a problem and could do with help from someone more knowledgeable.
The live p&l is calculated and updates live into a cell . I would like to show the highs and lows (daily, weekly etc) of this value in another cell(s). I guess that
I need to get this data so that it lists , then I can decide which time range I want to take for high and low (i.e. yesterdays) . The only thing I can find on the web and forums is where data is imported from an external source, nothing relating to manipulating data with excel itself , any ideas ?
p.s. I use Excel 2007
Excel as a position/money manager
-
Roger Rines
- Roundtable Knight
- Posts: 2038
- Joined: Wed Oct 06, 2004 10:52 am
- Location: San Marcos, CA
Excel has a built-in scripting language called VBA. In Excel 2000 the editor for the scripting area is located under the Main-Menu item Tools -> Macro -> Visual Basic Editor. I use the keyboard combination "Alt-F11" to get there. VBA has been around a long time, and is a very capable language for handling data manipulating task in Excel, and in many of the other MS Office modules.
Your assumption about needing to keep track of the data as it appears will be part of the solution. Without a history in some fashion, it will forget what it has collected each time you restart Excel.
To make this work on your end, consider finding a local VBA programmer who can see how your data connection is working, and can see a template of how you would like the spreadsheet to work. I've mentioned the template as a graphic example that you create as a static display of what you want when you are done (it doesn't need to be functional - just visual). With a vision of what you want, the person who will write the VBA modules can see what you have to understand what needs to be created to bring your current reality up to your need.
How history is collected and saved can be more than a simple text file. Microsoft has a database system called "SQL Server Express" they provide a free access license. This version has limitation, so you might find it won't work over the long term, but it should certainly work in the short-term. There are other database approaches as well, but the person you engage will be in a better position to advise you.
Your assumption about needing to keep track of the data as it appears will be part of the solution. Without a history in some fashion, it will forget what it has collected each time you restart Excel.
To make this work on your end, consider finding a local VBA programmer who can see how your data connection is working, and can see a template of how you would like the spreadsheet to work. I've mentioned the template as a graphic example that you create as a static display of what you want when you are done (it doesn't need to be functional - just visual). With a vision of what you want, the person who will write the VBA modules can see what you have to understand what needs to be created to bring your current reality up to your need.
How history is collected and saved can be more than a simple text file. Microsoft has a database system called "SQL Server Express" they provide a free access license. This version has limitation, so you might find it won't work over the long term, but it should certainly work in the short-term. There are other database approaches as well, but the person you engage will be in a better position to advise you.
-
babelproofreader
- Roundtable Knight
- Posts: 138
- Joined: Wed Nov 10, 2004 4:36 pm
You could use the macro recorder to record a VBA routine to simply automatically copy and paste the contents of the live updated cell into another column and then save. If you use the COUNT or COUNTA functions, the new values could be appended to the bottom of this column, thereby giving you a permanent copy of the streaming time series of live updated values.