Jump to content

Welcome to the new Traders Laboratory! Please bear with us as we finish the migration over the next few days. If you find any issues, want to leave feedback, get in touch with us, or offer suggestions please post to the Support forum here.

  • Welcome Guests

    Welcome. You are currently viewing the forum as a guest which does not give you access to all the great features at Traders Laboratory such as interacting with members, access to all forums, downloading attachments, and eligibility to win free giveaways. Registration is fast, simple and absolutely free. Create a FREE Traders Laboratory account here.

wje3

Trading with Market Statistics Using Excel and EOD

Recommended Posts

Hi Everyone;

This is my first post here. I have been reading Jerry's threads on Trading with Market Statistics with great interest. I have a few questions:

I am considering using market statistics yet using it for End of Day trading. I remember reading somewhere within the discussions that Jerry felt that this methodology would work on any time frame. I would be using a 20 day time period and closing prices. The 20 day period is somewhat arbitrary yet it is one month of trading days.

My questions pertain to using Excel to generate the relevant data points.I am no expert in Excel and usually rely on my children to walk me thru the formulae, but once done it's pretty straight froward.

Will Excel give me the correct data points, especially the SD of the VWAP ? The PVP, and VWAP calculations are straight forward. But after all the lengthy discussion about variance and calculating the standard deviation of the VWAP, I want to make sure that what Excel says is the SD is valid.I would use the 20 days of VWAP to calculate the SD.

I'm not a statistician/mathematician nor an Excel expert. I'm just trying to understand any limitations of the statistical tools that Excel uses.

Thanks in advance for your help.

Bill

Share this post


Link to post
Share on other sites

Yes if the formulas are correct. Does excel have a weighted SD? You might need to do a bit of dinkering to get that.

 

I would be as concerned about PVP (and lesser extent VWAP). If you are using the closing price of the daily bar you will be lumping all the volume for the day at that one price point (the close). If you can sample data every hour (or better still every minute) to get the volume distribution at price things will be more accurate. No reason not to do the calculations once per day but I would personally be happier with a sampling rate that is higher.

Share this post


Link to post
Share on other sites

Hi BlowFish;

Thanks for your answers.

Regarding the once a day sampling rate,lumping all the volume in for the closing price-yes I am a little concerned about that as it isn't very "granular". Yet, the underlying theory is the same as with two minute bars,if I understand it correctly. With two minute bars, all the volume is attributed to the close of that interval. With daily bars, it's the same underlying concept. I could do an average price for the day which might be more reflective of the days volatility.

Actually, what I am more concerned with is the statistics of the standard deviation. Why do I need a weighted SD. If I am trying to determine the SD for the VWAP, that series of numbers(the VWAP) is already weighted. I'm just trying to determine the SD of a series of numbers. Why does the SD need to be weighted as well?

Thanks for your help.

Bill

Share this post


Link to post
Share on other sites

Hi Bill, sounds like you understand the sampling issues. If you are looking 20 days back thats only 20 samples. As you correctly observe Jerry samples every 2 minutes. He talks about what he considers enough data....from memory he likes to see at least the bands open up wider than the bars themselves. I dunno I would have thought things are only just going to be settling down after 20 bars, it's a fairly small sample.

 

I did some comparisons (a long while back) of Jerry's 2 minute sample and raw tick data (so completely accurate) usually the VWAP & SD's where negligibly different but occasionally there would be a 2 or 3 ticks difference. The PVP is a bigger issue. It is very sensitive to how you allocate the volume how you sample and accumulate the peak can completely alter the PVP. This is because it jumps around based on the highest peak. Incidentally the way Ensign (which Jerry uses) allocates volume to PVP is that it pro ratas it to every level/tick between the high and the low of the bar. Allocating all the volume to a single price will likely give quite some odd results.

 

Jerry talks about why the SD's should be weighted by volume in the threads.

Share this post


Link to post
Share on other sites

Hi BlowFish;

Thanks for the information. I agree that the 20 day sampling is inadequate. There are large vacant areas on the volume histogram. I'm going to continue looking at the feasibility of using this on a larger time frame, e.g. daily

I'll also re-read the threads on the weighted SD.

Thanks again for your help.

Bill

Share this post


Link to post
Share on other sites

Hey Bill,

 

Even if you want to look at the longer time frames if you can build what you need with more data (like hourly perhaps) you should be golden. You might want to look at Ninjatrader you can get it free and there are indicators for VWAP SD and PVP in the public domain. of course there may be other reasons that you are leaning towards excel in which case that might not make sense for you.

Share this post


Link to post
Share on other sites

Hi BlowFish;

Funny you should mention NinjaTrader. I just downloaded it last night and I'm going to start playing with the charts. 20 days of hourly data might work but I'll take a look at it over the next few days.

Thanks for the suggestion.

Bill

Share this post


Link to post
Share on other sites

Thanks BlowFish;

I have a slightly related question regarding NinjaTrader. If I want to generate MP charts(TPOs and Volume Profiles) using NinjaTrader and the FinAlg add-on, do I only pay the one time FinAlg fee (plus data fees) if I'm running it on the free NinjaTrader downloaded program?

Are there any other fees from NinjaTrader to do this?

It's not clear to me from the NinjaTrader website.

Thanks for your help.

Bill

Share this post


Link to post
Share on other sites

Hi Bill,

 

Finalg is one of payment.

 

Ninja licensing is a bit odd. It's free if you have a live account with one of there brokers (Mirus & Amp being the most common futures one). Certain things (that your probably wont need) cost extra (one off) license fees. Multibroker support springs to mind. I am not sure if there is some sort of minimum required account activity, I do not believe so. Many people just sign up for a free demo account with the aforementioned brokers when they expire they re-sign. From that point of view it is completely 'free'. I think nowadays Mirus prevent you signing from the same email address within a month.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Topics

  • Posts

    • $CSCO Cisco Systems stock, nice top of range breakout, from Stocks to Watch at https://stockconsultant.com/?CSCOSEPN Septerna stock watch for a bottom breakout, good upside price gap
    • $CSCO Cisco Systems stock, nice top of range breakout, from Stocks to Watch at https://stockconsultant.com/?CSCOSEPN Septerna stock watch for a bottom breakout, good upside price gap
    • MNST Monster Beverage stock, top of range breakout above 60.45, from Stocks to Watch at https://stockconsultant.com/?MNST
    • ...hallucinates.... Student: “What if we gave the monkey LSD?” Guru: “The monkey already did LSD”
    • Question: To those that had/have cancer, what were the signs that made you think “something is not right here” to make you go see a doctor? Answer: So, 5/25/2018, I woke up, got ready for work, and as I walked to my car, I started gagging. Like something was stuck in my throat and I needed to clear it. And then it went away.   But 10 minutes after that, I was T-boned at 40mph on the driver side door. But what made me see a doctor was while my muscles felt better and bruises were going away, the gagging still continued, I started having fevers, my neck felt swollen, I was having such a hard time breathing, and I'd have random sharp pains in my chest, but not from where the seat belt saved me.   2 weeks after the accident, I finally see an urgent care doctor, who looks me over, tells me I'm fine, but luckily requests a neck X-ray. And I ask for a chest X-ray, which he rolls his eyes but let me have (most of my pain was in the neck, so I understand).   The very next day, he calls and says “So, that chest X-ray shows there's a 4 inch mass on your heart and lungs, and your lungs have been filling up with fluid, as well as in your pericardial (heart) wall. We need you to come in tomorrow.”   Turns out the big mass, due to the accident, caused my heart and lungs to tear and fill with fluid, the swollen neck and gagging was caused by 2 metastasized tumors, and the fevers and weight loss were symptoms. Stage 4b Hodgkin's Lymphoma.   But thankfully, we went very aggressive with chemo (and had a lot of bad side effects that don't normally happen to patients), and now I'm about 16 months cancer-free. Yay lucky X-rays! Rachel Jurina, Quora Source: https://www.quora.com/To-those-that-had-have-cancer-what-were-the-signs-that-made-you-think-something-is-not-right-here-to-make-you-go-see-a-doctor   Profits from free accurate cryptos signals: https://www.predictmag.com/  
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.