MP on excel

[ryker 10]

ryker, bobajob,

 

KUDOS to both of you for doing such a great job... but more importantly being so generous in sharing it.

 

What I'd like to do is link your sheets to DDE link for esignal, so that I can have it dynamically updating during the trading day.

 

How would I go about doing this?

 

I'm proficient with excel but not as versed in intricacies of programming it. Can either you point me in the right direction or walk me through it?

 

Thanks in advace!

 

Again, thank you in your generosity.

 

Agora.

 

I'm currently working on a version that will get data from csv file, so maybe it will be easier for you to update it?

DDE is not always good cause Worksheet_Change event in VBA is not fired by DDE changes (you've got to use another event).

Not finished yet, but will be ready soon.

[Agora 10]

ryker,

 

Great stuff. How would I go about hooking up your sheet to esignal through a DDE link?

 

Agora

[ryker 10]

ryker,

 

Great stuff. How would I go about hooking up your sheet to esignal through a DDE link?

 

Agora

 

How many symbols do you need to follow?

If it's a few something could be possible, you need to use Worksheet_Calculate Event, find which values has changed on the sheet and then update data sheet with values (you'll need to build 30 mins bars with that data).

It's not that difficult to do but you need to be a bit confident with Excel. I've tried to do something like this but stopped cause for a lot of symbols this is not efficient... I think I still have an example at work, will look tomorrow.

[Agora 10]

ryker,

 

Thanks. I probably just need to follow 2-3. ES, SP-full, mini-dow. But even one of those would be helpful like the mini-SP.

 

How do you tell excel that something new came into a cell and it needs to do something with it? That's probably where I need to begin.

 

Thanks for your generosity in sharing your knowledge.

 

Agora

[Agora 10]

ryker,

 

Also, I guess if you're doing a csv file, that might even be better since that'll hold the historical data. I would probably need to see several days of MP in order to determine the plan of attack and having excel recalculate the whole thing would be inefficient. It just need to do that once and then add new data as it comes in during the trading day.

 

Agora

[bobajob 10]

As I said in my post you can get a real-time import of data into excel from a web page - this is how I do my real-time charts

[gomes 10]

hey ryker,

I think something wrong from my end, with the same data, 4 time slices I don't have value in my MP. I will try to see what is problem, hope I can fix this.

Thanks for your help.

Regards

Humberto

[gomes 10]

Hey ryker,

I found the problem. I was putting the date data prefix so when I clear this I got it working properly finally. I am good to go now.

I have one more question if I may bother you.

What is the gray A in the first bracket, I am just curious about that.

Thanks for all of your efforts and time.

Regards

Humberto

[ryker 10]

Hey ryker,

I found the problem. I was putting the date data prefix so when I clear this I got it working properly finally. I am good to go now.

I have one more question if I may bother you.

What is the gray A in the first bracket, I am just curious about that.

Thanks for all of your efforts and time.

Regards

Humberto

 

Good

 

The gray A is the middle of the distribution, was wondering also if this is really necessary?

 

Was also wondering if I should add:

-- TPO count on the right of the distribution (is this useful?)

-- MP values at the top of the chart (under the date maybe?)

 

Any suggestions?

 

Thanks

[Agora 10]

As I said in my post you can get a real-time import of data into excel from a web page - this is how I do my real-time charts

 

bobajob,

 

How did you link it to a web site? Or is the website already configured to do this?

 

Agora

[bobajob 10]

Right - in quote.com you can export data to webpage. If you do this you get a webpage full of data. Turns out that the webpage is always there at the same address -it just updates. So you can go into any excel spreedsheet and go to Data - Import External data - new web query. Then type or copy the address of this webpage with the data. You can then select the data from the page that you wan to import.

It will take a few seconds and then appear in the sheet. Then you can refer to this data for your own purposes. You do have to update the sheet yourself - although I'm sure with VBA you can write a script to do this. I haven't myself yet but I will soon. I would give you mine but it is linked to my quote.com account for which I pay for, so I'm not giving that away.

[Agora 10]

Right - in quote.com you can export data to webpage. If you do this you get a webpage full of data. Turns out that the webpage is always there at the same address -it just updates. So you can go into any excel spreedsheet and go to Data - Import External data - new web query. Then type or copy the address of this webpage with the data. You can then select the data from the page that you wan to import.

It will take a few seconds and then appear in the sheet. Then you can refer to this data for your own purposes. You do have to update the sheet yourself - although I'm sure with VBA you can write a script to do this. I haven't myself yet but I will soon. I would give you mine but it is linked to my quote.com account for which I pay for, so I'm not giving that away.

 

boba,

 

Thanks for a great explanation. I'll have to see if I can somehow link esignal with their DDE.

 

Agora

[gomes 10]

Good

 

The gray A is the middle of the distribution, was wondering also if this is really necessary?

 

Was also wondering if I should add:

-- TPO count on the right of the distribution (is this useful?)

-- MP values at the top of the chart (under the date maybe?)

 

Any suggestions?

 

Thanks

 

Hey ryker,

Yes I like to see that gray price, so I can easily see if the action is on the upper half or bottom half of the profile, I think it is necessary for me.

TPO count and MP values would be super duper feature. I was going to ask you if you can add this feature but I didn't want to ask to much, you already share your hard work. I was kinda afraid to ask.:o If you can add this features will be very much appreciated.

Thanks in advance

Regards

Humberto

[Agora 10]

samde,

 

So is your excel sheet updating in realtime? Or is it manually inputed?

 

Agora

[milind_pol 10]

One more way of getting data - Accessing Web Pages Using Excel VBA

[bobajob 10]

Nice one milind_pol - I'll use that in my VBA script

[gojun077 10]

Kudos to bobajob on a great MP spreadsheet. It was just brilliant of you

to think of using CONCATENATE to create the row of letters from the separate letter columns! I have made a small addition to your

spreadsheet "ES Profile Maker Next Gen."

 

I added a feature that will calculate volume per tick using tick data for

the KOSPI200. In bobajob's current implementation, the profile appears

perfectly, but there is no volume information for how many contracts

were traded at each price point. I hope everyone will find this minor

addition useful. This feature was added using the excel function SUMIF.

 

Cheers,

Jun

MP2-26-07 example.rar

[notouch 10]

Am I right in thinking that the only way to use bobajob's spreadsheet is to input the TPOs manually? If so I'm going to produce my own Excel VBA code that produces the profile automatically from IB data without the need to be fumbling around inputing letters into a spreadsheet. I like ryker's design but unfortunately (and understandably) he's password protected the VBA code. I think I can do the whole thing from scratch though.

 

Edit: ah, I see how bobajob has done it, using the formula box. It seems you still have to enter the data for each column manually. I'm going to automate the whole process with IB data and VBA.

[bobajob 10]

Hi guys thanks for the feedback - I've actually got two different versions of my software now. The most refined version uses buttons to automate the process, but it is a learning tool. Therefore it does not use realtime data. The program essentially allows you to replay the day to see how the profile is developing. The idea is that you can teach yourself to recognise patterns in the market. It is really trying to give you years of market experience in a very short time. I have had some success with this but it seems my trades are very short term - usually involving trading at the extreme of a bell distribution and closing the trade at the centre of the distribution. This kind of trade happens low risk over and over again in a day.

 

I was hoping to sell that spreedsheet for a very small price - but I would be happy for a few people to try it out. Therefore if you PM me I will send a few copies out - provided i get some feedback.

 

The next version is my realtime one. As I have mentioned previously my version updates from Quote.com. The reason the one you guys have doesn't is that it will access my account and obviously I can't send that out to people.

 

I love that you guys are modding it to do other things - that's great. I have previously done implementations with volume but can't for the life of my get reliable volume signals for trading. If someone could explain some I would be grateful.

 

Regards

[ryker 10]

Am I right in thinking that the only way to use bobajob's spreadsheet is to input the TPOs manually? If so I'm going to produce my own Excel VBA code that produces the profile automatically from IB data without the need to be fumbling around inputing letters into a spreadsheet. I like ryker's design but unfortunately (and understandably) he's password protected the VBA code. I think I can do the whole thing from scratch though.

 

Edit: ah, I see how bobajob has done it, using the formula box. It seems you still have to enter the data for each column manually. I'm going to automate the whole process with IB data and VBA.

 

Hi notouch,

I'm currently working on a realtime spreadsheet (working with bloomberg) but also planning to allow realtime update from another third party, I'm doing this via a file that need to be updated. I can't say when it will be ready but I think it won't take that long. I'll be happy to share it but concerning the code I'm not really sure yet... But this should allow you to use my spreadsheet with IB. I've done some nice improvements since last version. I will post here as soon as I've last version.

 

Here:

© CJoint.com, 2007 is a screenshot of an autoupdateable version with last day splitted (can choose to merge it to obtain a normal profile).

 

 

++

[rok 10]

Any updates Ryker?

[ryker 10]

Hi,

I'll post the new version this week, I've tested it for a couple of weeks and it looks ok now.

[ryker 10]

Here is the last version.

 

Setup to use it:

- First things to do is to install necessary addins (Analysis tools and Analysis tools - VBA).

- You need to setup your own list of securities by following the examples (you can change the bloomberg ticker by the symbol code you're usually using).

- Import your own data (must be 1min data): copy your filed into the import folder, rename them (something like: symbol.csv), and then press the Update data button (could take a while, you could check excel is still processing by looking into the logs).

- Do not try to import more than one year in one time.

 

How to use it:

- You have to make sure Begin and End date correspond to some trading day (if not program could crash, still had to be improved there...).

- If you want to have an autoupdate (button EnableAuto), you need to have a program which update the file called symbol..csv) every minutes in the folder AggregatedDay.

- Split button shows the last day in a splitted view (only work with the last day).

- Merge shows a normal distribution profile.

- Do not try to plot too many days or you'll hit excel limitations.

- Enable auto works properly in a splitted view, and doesn't work really well in a merged view (I'm not using it in realtime).

 

This is a quick description will try to improve it a bit later. I've inserted some screenshots of the different views available.

 

Ryker

TL-MP.rar

[rok 10]

A million thanks!

rok

[ryker 10]

I don't seem to be able to edit my post again...

 

So a few more thing:

- Format in the file to import data:

DateTime,High,Low,Open,Close,Volume

01/03/2007 07:00:00,115.83,115.83,115.83,115.83,0

Don't include first line with header.

- Edit on the name of the file for autoupdate: yyyymmdd.symbol.csv

Same format as above.