Detailed Stock Data to Excel

[SctN]

Hi people,

New here, so apologies if I'm posting in the wrong area or reposting something that exists (I did do a search!)

Basically I'm looking at how to get detailed stock data into a spreadsheet so I can run some models and do some "paper trading". I've got OHLC data already [thx yahoo finance], but I'm concerned that if I assume I can always sell based on the range between H & L this may be inaccurate due to the volume at the peaks.

e.g. I want to sell at very close to the H, but the volume there is very low.

So ideally I'm wanting data with volumes at the sell values throughout the day. Obviously I can't get everything, but I assume there is a happy medium between OHLC (e.g. once a day) and 1 secondly data (e.g. 21,600 per day).

any tips? links?

Cheers.

 

[Gringotts Bank]

Hi people,

New here, so apologies if I'm posting in the wrong area or reposting something that exists (I did do a search!)

Basically I'm looking at how to get detailed stock data into a spreadsheet so I can run some models and do some "paper trading". I've got OHLC data already [thx yahoo finance], but I'm concerned that if I assume I can always sell based on the range between H & L this may be inaccurate due to the volume at the peaks.

e.g. I want to sell at very close to the H, but the volume there is very low.

So ideally I'm wanting data with volumes at the sell values throughout the day. Obviously I can't get everything, but I assume there is a happy medium between OHLC (e.g. once a day) and 1 secondly data (e.g. 21,600 per day).

any tips? links?

Cheers.

"I want to sell at very close to the H". The high isn't known until the bar is closed, regardless of time frame, and by then it's too late.

Weblink has intraday data for ASX stocks.

 

[SctN]

"I want to sell at very close to the H". The high isn't known until the bar is closed, regardless of time frame, and by then it's too late.

Weblink has intraday data for ASX stocks.

Guess that sentence wasn't clear. I meant if my model said sell at X and X was very close to the days high.

Appreciate the help despite appearing like a nub.

Any idea on how much volume $$ I would want to see at a given value to assume my sale of. Lets say $50k, went through? I'm presuming I can't assume if the volume is equal to my sale I get the sale...

 

[Gringotts Bank]

Any idea on how much volume $$ I would want to see at a given value to assume my sale of. Lets say $50k, went through? I'm presuming I can't assume if the volume is equal to my sale I get the sale...

If it's BHP, then you will want 50000/23.3=2145 shares to be traded at your exit price of x. Participating in the market will influence the market to some* degree. And you're right, you can't assume that if the volume is available that you will get the price you want. That's called slippage and you can allow for it in your testing.

*the smaller the ratio: my trade size: $turnover per hour, then smaller the slippage. Your average hold time will also influence the impact of such slippage on profitability. Day trading 50k of BHP would create a small but noticeable impact on profitability, I'd imagine.

 

[SctN]

Thanks, I must admit I assumed a trade size as small as $50k would go unnoticed in a market as large as BHP.

I'll have a search on this forum now you have given me the terminology I was missing. But do you know of a book or alternative resource that would help me understand the fundamentals of slippage and impacts of trading on underlying market trends?

Cheers.

 

[Gringotts Bank]

Thanks, I must admit I assumed a trade size as small as $50k would go unnoticed in a market as large as BHP.

I'll have a search on this forum now you have given me the terminology I was missing. But do you know of a book or alternative resource that would help me understand the fundamentals of slippage and impacts of trading on underlying market trends?

Cheers.

Slippage is a simple concept and you won't need a book on it. There's two basic ways to allow for it:

1. add a certain amount to brokerage costs
2. if x is your target sell price, run your backtest on x-.05 or whatever you choose.

Or you can just trade your system in real time and see how profits compare with backtests and just understand there will be approximately that amount of difference. Don't spend too much time on this aspect unless you are trading very short time frames and every tick is important. If that's what you're wanting to do, then you need to know bid/ask prices intraday, and it gets complicated. Howard Bandy's books worth looking at, although he uses Amibroker for the most part.

Yahoo data, being free, won't let you know if the transaction occurred at the bid or ask price.

 

[SctN]

Awesome. Thanks for all the info. Think you are right, time to give it a crack.

So I'll wait for this election to be resolved. Then have at it.

 

[WillyWonka]

Try This

https://www.aussiestockforums.com/forums/attachment.php?attachmentid=62141&d=1427588533

You would need to refresh a few times during the trading day to get your price range, then just save the data to a new sheet.

 

[SctN]

Try This

https://www.aussiestockforums.com/forums/attachment.php?attachmentid=62141&d=1427588533

Wow, awesome work WW. To be honest when you first posted this I was a tad suss downloading a spreadsheet with macros etc.
So I checked out your original thread.

I couldn't get to work on my work pc (seems to bring back the codes in column A, random html code in column B & nothing else) but I'll try @ home tonight see if it works better without firewalls etc. I'll also try once the market opens in case it needs live data to work.

thanks for sharing.

 

[WillyWonka]

Wow, awesome work WW. To be honest when you first posted this I was a tad suss downloading a spreadsheet with macros etc.
So I checked out your original thread.

I couldn't get to work on my work pc (seems to bring back the codes in column A, random html code in column B & nothing else) but I'll try @ home tonight see if it works better without firewalls etc. I'll also try once the market opens in case it needs live data to work.

thanks for sharing.

The sheet was working for me when I just downloaded it now, I am using excel 2007. It dose not work on the Macintosh OS as the Mac excel dose not support vba scripts. You are right to be cautious about macro enabled sheets, this one however is 100% safe, it is also fully unlocked so the code can be viewed for anyone who wants to inspect its safety.

 

[SctN]

The sheet was working for me when I just downloaded it now, I am using excel 2007. It dose not work on the Macintosh OS as the Mac excel dose not support vba scripts. You are right to be cautious about macro enabled sheets, this one however is 100% safe, it is also fully unlocked so the code can be viewed for anyone who wants to inspect its safety.

Tried again at home, works perfectly. Must be the work firewalls.

Ripper, thx for the spreadsheet will be very helpful.

 

[skc]

Thanks, I must admit I assumed a trade size as small as $50k would go unnoticed in a market as large as BHP.

I'll have a search on this forum now you have given me the terminology I was missing. But do you know of a book or alternative resource that would help me understand the fundamentals of slippage and impacts of trading on underlying market trends?

Cheers.

There is no single source of truth when it comes to slippage, it depends on lots of factors like the size you trade, underlying stock liquidity, face value of the stock (whether it's $100 or 20c), time of the day to tiny details like how you/your broker place your trade, how fast is your internet connect etc etc.

You will only get to know how these factors affect your particular trading by gaining more experience (through watching and doing).

You should definitely learn about minimum price step of different priced stocks and about spread... so take a look at CBA (~$75), AMC (~$14), PTM ($6), HSO (~$2), FXJ (~80c) and see how easy or hard it is to trade the "last" price vs sell at the highest bid.