Anomandaris
05-25-2009, 04:32 AM
Hi guys,
I need some advice on how i should organize this spreadsheet....
So on the main page it will have a set of option boxes and dropdowns to select data and then press a button to run a macro that
produces a chart...I've got the graphing code figured out for one set of data, but I'm confused about how to link all the other data.
You may see the attached spreadsheet to get an idea of what the main page would look like.
Data selection-
At the top there are 2 dropdown boxes, where you can either put one market against the other (eg US vs UK) to get the difference/spread, or you can set US vs US, EUR vs EUR to simply analyse data from that single market.
So 1st step is to select a market - US, UK, JPN, or EUR
Then again there are 2 dropdowns.
Then you have to select one of 5 alternatives .
A,B,C,D,E ----so there are 5 ranges of data for each of the markets making it a total of 20 ranges (is it easier to name the ranges and refer to them using the name?)
So if you have US - Alternative C and EUR -Alternative D
then the following option box should have these options:
1-US ALternative C (just analyses US alternative C data)
2.EUR alternative D (this option just analyses EUR alternative D data range)
3. US alternative C vs EUR alternative D (this is the spread, the US data - EUR data )
So I'd have to select one of these options from the box
then next step is a charting criteria
2nd set of option boxes(select 1)
-average
-standard deviation
-zscore
at this point i'm done --I should click a button to produce a chart.
The yellowboxes are the dropdowns, i dont know why i wasnt able to find the option to create dropdown boxes. And FYI the ranges are set by date, i've shown a typical one on sheet 2...So when doing spreads it would be the difference on a certain date, and if the cell was a blank then we would ignore that date and that observation.
I'm not sure how these dropdowns and option boxes can be linked along with the data, any suggestions?
thanks
I need some advice on how i should organize this spreadsheet....
So on the main page it will have a set of option boxes and dropdowns to select data and then press a button to run a macro that
produces a chart...I've got the graphing code figured out for one set of data, but I'm confused about how to link all the other data.
You may see the attached spreadsheet to get an idea of what the main page would look like.
Data selection-
At the top there are 2 dropdown boxes, where you can either put one market against the other (eg US vs UK) to get the difference/spread, or you can set US vs US, EUR vs EUR to simply analyse data from that single market.
So 1st step is to select a market - US, UK, JPN, or EUR
Then again there are 2 dropdowns.
Then you have to select one of 5 alternatives .
A,B,C,D,E ----so there are 5 ranges of data for each of the markets making it a total of 20 ranges (is it easier to name the ranges and refer to them using the name?)
So if you have US - Alternative C and EUR -Alternative D
then the following option box should have these options:
1-US ALternative C (just analyses US alternative C data)
2.EUR alternative D (this option just analyses EUR alternative D data range)
3. US alternative C vs EUR alternative D (this is the spread, the US data - EUR data )
So I'd have to select one of these options from the box
then next step is a charting criteria
2nd set of option boxes(select 1)
-average
-standard deviation
-zscore
at this point i'm done --I should click a button to produce a chart.
The yellowboxes are the dropdowns, i dont know why i wasnt able to find the option to create dropdown boxes. And FYI the ranges are set by date, i've shown a typical one on sheet 2...So when doing spreads it would be the difference on a certain date, and if the cell was a blank then we would ignore that date and that observation.
I'm not sure how these dropdowns and option boxes can be linked along with the data, any suggestions?
thanks