How to convert tick data into Candlestick chart

[kesk]

Dear friends,

I am writing a personal application extracting NSE data from Yahoo website to be used as a Intraday quote application. I have added database functionality and a line chart feature function. So far this works. If people are interested, i am willing to share the application with others.

I also need some help: How to convert the tick data to OHLC values for a 1 min chart ? I am doing the program in vb.net. Any ideas/suggestion would be greatly appreciated. If you need to contact me personally means, pls use this id : kesk32 at yahoo co in

Thanks

kesk

 

[kesk]

Would updating the data with tick data every 5 sec and then deciding the O,H,L,C values from a 1 min query and populating the database work ?

Any ideas?

kesk

 

[rajendrani]

Dear friends,

I am writing a personal application extracting NSE data from Yahoo website to be used as a Intraday quote application. I have added database functionality and a line chart feature function. So far this works. If people are interested, i am willing to share the application with others.

I also need some help: How to convert the tick data to OHLC values for a 1 min chart ? I am doing the program in vb.net. Any ideas/suggestion would be greatly appreciated. If you need to contact me personally means, pls use this id : kesk32 at yahoo co in

Thanks

kesk

Buddy, I guess when you have line chart function which uses only the close price or the ltp, for candlestick you also need to have the open, high, low values also, so just getting those values also will serve the purpose, then instead on line chart you can have a candlestick chart.

This would be of great help for you
http://67.220.225.70/~gumm5981/download-stock-prices.htm

 

[rajendrani]

Play around with the excel file and you will be able to get what you really want to

otherwise just go to

http://67.220.225.70/~gumm5981/INVESTING-7.htm

or just
http://www.gummy-stuff.org/ for a wealth of information.

thanks and regards,
rajendrani

 

[kesk]

Hi Mr Rajendrani,

Thanks for the info. The Open, High, Low and Close values that any page displays is the day's OHLC. If you use that, then almost all the candles will look similar as the O, H and L will remain mostly constant.

The quote or tick values in a minute period should be converted to OHLC values, thats what my question is all about. How to manage that, either in memory or in a database?

senthil

 

[Redeemer]

Hello Kesk,

Is your charting real time? And are you using a full fleged database? or a file based one like BerkelyDB or Access?

If it is real time, them do the ohlc calculations in memory. Cause you are drawing as you get the data, so no point writing to the DB.

If it isnt real time (as in, drawing the days chart at the end of the day) then either approach will do. The in memory approach might actually be more efficient, since not a lot of DB io/network access is happening (especially if not a file based DB)

Since you have 5 sec tick, so 60/5 == 12 data points.

So you data structure will need to be an aggregator, that accepts values one at a time, updates its internal strucutre (setting O, H, L, C values) and when it hits 12, you can write it to the DB or display it, and clear the structure for the new set.

Hope this Helps

 

[kesk]

Hi Reedeemer,

Thanks for your reply. I am not a full time programmer or programmer in the real sense, just a hobby programmer, so you can understand my shortcomings.

I am very good at database both in Access and SQL server and somewhat familiar with vb.Net. Hence i went ahead and wrote the program in Access and vb.Net. The program is real time, fetches data from Yahoo. The line drawing is Ok so far. Since candlesticks might give a better understanding of the prevailing market, I decided to incorporate that also, and thats where my limitation kicks in.

To my understanding, if we can store the values in a db all the intraday values, it might come in handy for looking back the resistance and support areas and thats why choose to save them.

Coming to the question of in-memory sorting/storing values for a min, i simply dont understand it. If you can post some kind of code, example, a URL, that would be very helpful to me.

If you are interested in helping me, please PM me or my email is 'kesk32 at yahoo dot co dot in'.

Once again thank you for your reply.

 

[terminator_123]

Sort tick data by time stamp. Loop through your data checking time stamp. Neglect seconds and keep track of high,low for current minute. At the end of the current minute, the last value wd be the close, first value of the minute becomes open.
For charting use zedgraph graph control (which has inbuilt support for candle sticks).

 

[Redeemer]

Hello Kesk,

I mostly hang out on programming forums, and would prolly get yelled at there for giving out detailed code (since we mostly prefer to explain stuff and then let a programmer think for himself)
But since this is not a programming forum and I'm bored.... its your lucky day

--For the DB: 
--(assuming you have made a standard instrument table to store data)
-- Note: DBs arent really my strong points, 
--         so you might actually have a better way of doing this

-- For H and L
SELECT MAX(price), MIN(price) from INSTRUMENT_INTRADAY 
WHERE series='EQ' 
   AND imnt='%SYMBOL%' 
   AND day='%DATE_YOU_WANT%' 
GROUP BY HOUR(day), MINUTE(day) 
ORDER BY HOUR(day), MINUTE(day)

-- For O and C
SELECT price AS "Open", from INSTRUMENT_INTRADAY 
WHERE series='EQ' 
   AND imnt='%SYMBOL%' 
   AND day='%DATE_YOU_WANT%' 
   AND SECOND(day) = MIN(SECOND(day))
GROUP BY HOUR(day), MINUTE(day) 
ORDER BY HOUR(day), MINUTE(day)

SELECT price AS "Close", from INSTRUMENT_INTRADAY 
WHERE series='EQ' 
   AND imnt='%SYMBOL%' 
   AND day='%DATE_YOU_WANT%' 
   AND SECOND(day) = MAX(SECOND(day))
GROUP BY HOUR(day), MINUTE(day) 
ORDER BY HOUR(day), MINUTE(day)

//For In memory

class Aggregator:
    members:
        float open
        float high = -1
        float low = +Infinity
        float close
        
        int tick

    methods:
        // If this method returns true, 
        // then you get the ohlc from it, and display it
        addTickData price
            if tick == 0
                resetOHLCValues

            periodOver = false
            increment tick

            if tick == 1
                open = price

            if tick % 12 == 0
                close = price
                periodOver = true
                tick = 0

            high = Max(high, price)
            low = Min(low, price)

            return periodOver

 

[chaitu18]

Hello Kesk,

I mostly hang out on programming forums, and would prolly get yelled at there for giving out detailed code (since we mostly prefer to explain stuff and then let a programmer think for himself)
But since this is not a programming forum and I'm bored.... its your lucky day

--For the DB: 
--(assuming you have made a standard instrument table to store data)
-- Note: DBs arent really my strong points, 
--         so you might actually have a better way of doing this

-- For H and L
SELECT MAX(price), MIN(price) from INSTRUMENT_INTRADAY 
WHERE series='EQ' 
   AND imnt='%SYMBOL%' 
   AND day='%DATE_YOU_WANT%' 
GROUP BY HOUR(day), MINUTE(day) 
ORDER BY HOUR(day), MINUTE(day)

-- For O and C
SELECT price AS "Open", from INSTRUMENT_INTRADAY 
WHERE series='EQ' 
   AND imnt='%SYMBOL%' 
   AND day='%DATE_YOU_WANT%' 
   AND SECOND(day) = MIN(SECOND(day))
GROUP BY HOUR(day), MINUTE(day) 
ORDER BY HOUR(day), MINUTE(day)

SELECT price AS "Close", from INSTRUMENT_INTRADAY 
WHERE series='EQ' 
   AND imnt='%SYMBOL%' 
   AND day='%DATE_YOU_WANT%' 
   AND SECOND(day) = MAX(SECOND(day))
GROUP BY HOUR(day), MINUTE(day) 
ORDER BY HOUR(day), MINUTE(day)

//For In memory

class Aggregator:
    members:
        float open
        float high = -1
        float low = +Infinity
        float close
        
        int tick

    methods:
        // If this method returns true, 
        // then you get the ohlc from it, and display it
        addTickData price
            if tick == 0
                resetOHLCValues

            periodOver = false
            increment tick

            if tick == 1
                open = price

            if tick % 12 == 0
                close = price
                periodOver = true
                tick = 0

            high = Max(high, price)
            low = Min(low, price)

            return periodOver

Thanks for the script!! it is really helpful!!!