Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
I fixed that issue by leaving the Col F alone if there was an existing value.
I also wanted a way to track all my positions on the Tracker tab with one click. The code I came up with runs through each position and as long as all positions of the spread have the same number in Col F and are adjacent they will be calculated as a spread, one spread at a time. Then it calculates all the remaining positions individually. My code might not match up with yours because I have modified the Track Spread to accommodate more than two positions, but if you are interested I can get you the code.
Also, if you are interested in tracking several positions historically I'd suggest looking at running that through the Tracker rather than the Historic tab (use code to change date on Scanner, track positions on Tracker, and save the data ... then examine results in a pivot table).
Can you help answer these questions from other members on NexusFi?
I have not modified any of the code in my copy of your file. Can you post how you fixed the code so Col F is not modified by calMargin(3,0).
Yes, I am interested in getting the code you have for updating all positions on tracker with one click. I am sure we have both done it slightly differently. But looking at both may produce the better code.
Yes, in my shell code I have tracker positions being record historically for each EOD Risk File, so that positions can be seen through DTE.
Here's what I came up with. The fix for the Col F is built into the modified CalcMargin sub.
It will no longer prompt you to select each side of the spread. It is now like the Track Select ... highlight the positions in your spread and then click the Track Spread button. If it is a new spread (no number in Col F) it will give it a number. If it already has a number in Col F it leaves it as is. I've tested it with spreads that have four legs and it works fine, theoretically it should be able to handle as many legs as you want in a spread (the next step for true portfolio margin calculation). It will also add two new columns of info: AE is the current ROI if position was exited early and AF is the current $ P/L.
It should be a simple swap-out of the existing subs in your spreadsheet with those attached ... just let me know if you have any trouble with it.
If you don't mind sharing, how are you analyzing the data you get tracking multiple positions through the tracker ... are you just running it in a pivot table or something else?
Your modified calcMargin has a call to a new function called optionvalue. Could you post this missing code.
Yes I am happy to share what I am doing with my shell code optionanalysis.xls. I will post this later. Essentially I am looking at the data your engine provides and processing it graphically and statistically.
Not sure what your plans are for your code but it has been a great help.
Is the code to scan risk files in your default directory of any value?
I am very happy to contribute whatever I can as a sign of appreciation for the work you have already done.
You probably can already tell that I am not essentially a coder. I am more of a app designer and architect. But I can code and have been for 15 years to support my trading activity. My code will just be a little messy and in the past has be reworked by coders after a prototype or design stage (app work I have done in CRM during my career).
Here are the missing functions. If there are too many more missing parts I can look to post my latest spreadsheet (after a while you lose track of all the changes you make).
I am looking forward to seeing the way you are processing and analyzing the data. Right now I mainly use this for risk management, but I have been looking more at back-testing ideas ... mainly with spreads. That and I'd love to have full portfolio margin calculation, but right now that looks pretty daunting.
I'm not sure I fully understand your question. Were you asking about the code I use to list the pa2 files? If so, I have included that with the attachment ... if not please help me to understand what you asking.
Thank you ... I always love getting a glimpse at what other people are doing with their analysis.
I will test the Listpas2Files Procedure. It is similar to the code for the same task that I posted earlier. But it actually reports the date range for the saved risk files. Yours rebuild the list in Col A. My approach is to say you don't need Col A. If you know the date range of your save risk files (which my code reports to the user in the input box) then the dates for the selected date range can be directly entered into Col F. At this moment my approach does not remove the dates with no returned data. Visually I find this easier to look at in bock so weeks. My approach would be then to removed dates with no returned data when I take the time series and move it for analysis. This is because when presenting the time series in a chart two axis's are required. I chart vol and delta on one axis and option price on the other. why because of the different numbers, it is easier to reformat vol and delta to normal decimal numbers e.g. vol at 20% becomes .2 and delta of 20 becomes .2 - therefore the two values are compatible for assignment to a secondary access in a chart which then leaves the primary axis for option price. (let me know if this is not clear.) So you will be able to deduce that I reprocess the data from tracker and historic into newly formatted data sets for analysis.
Use for analysis. Yes we are thinking alike. I see your file useful for both risk management and candidate search.
The ability to retrieve both snapshot and time series data sets is the key. An ability without the work you have down I would not have access too. Again thank you.
Yes, if you wish to develop the code further and I can help maybe a current XLS SPAN file is a good idea.
Okay I see you have removed the input boxes. Now you simply need to select the vertical cels for the spread you wish to trade and click track spread. This great.
The code I have goes the next step. if there are 10 spreads in tracker, with one click the are all process without any need for the user to selected any cells.
If you do any grains you'll need the GrainValue function as well.
I went the lazy way ... it was the easiest way to get things done. If I wanted to test how an idea would have worked last year at this time, I set my historic dates to start on 5/10/13. I have no idea what day of the week that was, but the code will find the closest pa2 file I have in my folder and then give me dates for the next 30 trade dates. I understand the angle you are going, I just found it easier to simply have a list of the dates available and grab from that. It's a horse apiece I guess.
I left the rough code I have been using for back-testing along with a sample of the results on the Tracker tab so you can see the route I was taking. Unfortunately, you have to dig through the code in the Backtest module a bit (I don't have all the user-friendly buttons for these subs).
I have focusing primarily on the price to see if an idea is viable, but I could see where incorporating other variables would be beneficial. Any ideas on tweaking the back-testing would be appreciated.
The code that is in for Track All will calculate for spreads and individual positions alike. Once a spread has been identified (you used Track Spread on multiple rows and they now have a number in Col F), click Track All and it will calculate each spread down the list and then the remaining individual positions. Try it on the one I uploaded to see what I'm talking about.
There's a couple of other tweaks in there from the last release, pretty much my "work in progress" spreadsheet.
Appreciate the new file. It is considerably larger and with a quick look at the code there is a bit to take in.
I feel that I should apologize. I see my suggestions where redundant. You are doing a great job on improving what is already a tool that has real value.
So rather than stepping on your toes I would be happy to take your direction on testing anything you wish to have checked.
The back-testing is a fantastic addition.
Here are two screen shows that are generated on the fly by the code in my option analysis file that utilises the data from your file. This is a snapshot. Based on the option chain.
No need to apologize. I hope I didn't come across as dismissing your ideas, I just hadn't thought about posting what I have been toying with in the spreadsheet until I saw that you had fixed some of the same issues I had found.
I don't really have any direction I am going with the spreadsheet right now. My main focus is to come up with some new trading ideas, so I have been looking at the back-testing. Normally, I come up with new tools as I run into issues/problems or when I see what other folks are doing.
From the looks of your spreadsheet, I highly doubt that you would be stepping on any toes with me ... screenshots look impressive. I must admit that I immediately wanted to take a look "under the hood" and see what you have in there. If/when your are willing to share some details, I'd love to see some the tools you have developed and get a glimpse of the way you are analyzing potential trades.
