This is for Wednesday February 4, 2009
NIFTY: Mixed cues really. EOM and TRIN are not looking bullish. The Elder Ray too is not bullish. Best is to make use of the rally due to global cues and exit longs or even shorts.
SENSEX: Same as the Nifty. Almost all sectors are showing very bullish adn very bearish cues. May be you could play hedged positions but going long or short without a hedge could be dangerous. Today though u can come out of the long positions or hedge it with a short.
DOW JONES: Mixed to tepid cues. May just consolidate.
CRUDE: Havent checked!.
Tuesday, February 3, 2009
Sunday, January 18, 2009
Link to the EOM file for NSE scrips only
http://www.4shared.com/file/81234537/12e0da73/NSE_EOM.html
Takes 10 mins to download. I have erased all formulae in all rows to make the sheet less bulky however we need formula in the last row at least so we can autofill for the next day's bhav. You can use the last row to autofill and populate the columns L to T for the newest NSE bhav data as follows.
Paste tomorrow's bhav just below the ZYLOG row and autofill from zylog last row for columns L to T taking care to stop at Zylog row. Then Sort on SYMBOL , SERIES, TIME STAMP with ascending and Header selected in ONE GO! All the cell references will adjust themselves. Now select all data in all rows except last row from column L to T and Click on COPY and then PASTE SPECIAL and paste the values to make the sheet less bulky . Retain the formula in the last row alone to autofill for next day's bhav. If no more data can be added when the number of available rows have run out, you have to filter the lastest data and then add bhav in a new sheet of the same excel file. That I will explain in a separate post. For now there is enough space to add the new bhav on Monday i guess.
http://www.4shared.com/file/81234537/12e0da73/NSE_EOM.html
Takes 10 mins to download. I have erased all formulae in all rows to make the sheet less bulky however we need formula in the last row at least so we can autofill for the next day's bhav. You can use the last row to autofill and populate the columns L to T for the newest NSE bhav data as follows.
Paste tomorrow's bhav just below the ZYLOG row and autofill from zylog last row for columns L to T taking care to stop at Zylog row. Then Sort on SYMBOL , SERIES, TIME STAMP with ascending and Header selected in ONE GO! All the cell references will adjust themselves. Now select all data in all rows except last row from column L to T and Click on COPY and then PASTE SPECIAL and paste the values to make the sheet less bulky . Retain the formula in the last row alone to autofill for next day's bhav. If no more data can be added when the number of available rows have run out, you have to filter the lastest data and then add bhav in a new sheet of the same excel file. That I will explain in a separate post. For now there is enough space to add the new bhav on Monday i guess.
Sunday, August 31, 2008
Link
The sites which will give you the data to update:
1. For CRUDE ETF is (USO symbol in YAHOO FINANCE, USO = US OIL)
http://finance.yahoo.com/q/hp?s=USO
Just copy latest data from the site and paste in the relevant row below the last row with ystday'd data, for other columns autofill from the previous row with formulae. No sort for this.
2. For NYSE NASDAQ TRIN
http://finance.yahoo.com/marketupdate?u
yahoo finance, just below the graph you get advancing declining data... simply fill it straight away because the data is readymade unlike for our Nifty. Do an autofill. No sort.
3. For our TRIN(Nifty Trin worksheet)
You will have to filter on today date in NIFTY LAST THREE DAYS worksheet, date is Column K and *NIFTY* entry in column CF and copy paste that data from A to K in NIFTY TRIN worksheet, autofill from the last row for ystday for the rest of the columns down until ZEEL. You dont have to do a sort here.
4. For DOWJONES again click on Dow in the above link given for NYSENASDAQ TRIN and then click on historical prices. Copy paste the latest data into the DOWJONES DIRECTION worksheet. Autofill from last row of ystday's data. No sort again for this.
5. For our FNO its bhav copy with the current series selected and only futures selected. Paste that data, do an autofill from the row above and then SORT.
To study any particular scrip, use the NSE LAST ONE MONTH worksheet, filter on that particular scrip and study the indications over a 10 day period.
Most worksheets have formulae only in the last row to make the file less bulky.
The NSE ONE MONTH file has formulae only in the last row and again only in the last two columns, other columns are just a copy paste of data from NSE LAST THREE DAYS file. So autofill only for the last two columns everyday... after autofill sort, and remove the formulae for all cells of the last two columns except the last row.
The indications play out only 30% of the times, dont take indications at face value. Use it for paper trades to hone your TA skills, or Moneybhai, donate 5% to a poor soul if you win something. Dont trade real money using this file. I am not responsible for your losses. This file is only a study in TA and I am learning myself. Indices play out almost immediately but individual stocks take 10-15 days to play out their bullish indications. If a stock is backed by 7 indicators as bullish any given day, it may play out only after sometime not the very next day. Dont get fooled taking these indications at face value. Study the NSE ONE MONTH FILE file with the historical data. The column CP gives the total number of indications backing a given scrip on any given day. See how the indications have played out, how accurate it has been. You will know how much time the indications take to play out and whether they play out at all. Ignore stocks with anything other than EQ or BE in column B. I have no clue what the other entries mean. Ask NSE. LOL
Unquote
1. For CRUDE ETF is (USO symbol in YAHOO FINANCE, USO = US OIL)
http://finance.yahoo.com/q/hp?s=USO
Just copy latest data from the site and paste in the relevant row below the last row with ystday'd data, for other columns autofill from the previous row with formulae. No sort for this.
2. For NYSE NASDAQ TRIN
http://finance.yahoo.com/marketupdate?u
yahoo finance, just below the graph you get advancing declining data... simply fill it straight away because the data is readymade unlike for our Nifty. Do an autofill. No sort.
3. For our TRIN(Nifty Trin worksheet)
You will have to filter on today date in NIFTY LAST THREE DAYS worksheet, date is Column K and *NIFTY* entry in column CF and copy paste that data from A to K in NIFTY TRIN worksheet, autofill from the last row for ystday for the rest of the columns down until ZEEL. You dont have to do a sort here.
4. For DOWJONES again click on Dow in the above link given for NYSENASDAQ TRIN and then click on historical prices. Copy paste the latest data into the DOWJONES DIRECTION worksheet. Autofill from last row of ystday's data. No sort again for this.
5. For our FNO its bhav copy with the current series selected and only futures selected. Paste that data, do an autofill from the row above and then SORT.
To study any particular scrip, use the NSE LAST ONE MONTH worksheet, filter on that particular scrip and study the indications over a 10 day period.
Most worksheets have formulae only in the last row to make the file less bulky.
The NSE ONE MONTH file has formulae only in the last row and again only in the last two columns, other columns are just a copy paste of data from NSE LAST THREE DAYS file. So autofill only for the last two columns everyday... after autofill sort, and remove the formulae for all cells of the last two columns except the last row.
The indications play out only 30% of the times, dont take indications at face value. Use it for paper trades to hone your TA skills, or Moneybhai, donate 5% to a poor soul if you win something. Dont trade real money using this file. I am not responsible for your losses. This file is only a study in TA and I am learning myself. Indices play out almost immediately but individual stocks take 10-15 days to play out their bullish indications. If a stock is backed by 7 indicators as bullish any given day, it may play out only after sometime not the very next day. Dont get fooled taking these indications at face value. Study the NSE ONE MONTH FILE file with the historical data. The column CP gives the total number of indications backing a given scrip on any given day. See how the indications have played out, how accurate it has been. You will know how much time the indications take to play out and whether they play out at all. Ignore stocks with anything other than EQ or BE in column B. I have no clue what the other entries mean. Ask NSE. LOL
Unquote
Saturday, August 30, 2008
Updating ***NSE LAST THREE DAYS*** WORKSHEET
Quote
Those of you still interested in my complex excel file should learn how to update. The following exercise though looks intimidating is a breeze, keep trying and practice.. it takes just 10 minutes actually though i had to write a story. LOL.. you should practice with the file you downloaded with 28th data coz u have to add 29th data... the present file in the site is already updated so that wont help for ur practice session.
All worksheets have formulae only in the last row as we only autofill from the last row down. No point in keeping formulae in the other rows as we only want the values, so i have deleted all formulae in all other rows to make it less bulky. All worksheets preferably also have windows freeze pane option chosen, if not do it yourself, Click on C2 or B2 and then click on Windows Freeze panes so when u scroll u can still look at the header and the scrip/date ur referring to.
***Remember this major flaw in excel: in filtered state if you copy and paste with one click the filtered data gets copied correctly.. but if you click paste twice with the same clipboard then all the data gets copied and you will wonder why when all you wanted was filtered data. If this happens to you, delete the pasted data, once again go to the file from which you have to copy in the filtered state, copy fresh and paste it in the new place with a single click.****
There are many COUNT columns, for any given day if its BUY in the previous column the next column COUNT gets 1 point, if its SELL it gets -1. There are many DAY columns you might wonder why. Its because I began tracking the indicators on different days. Had I tracked them all together, I could have maintained only one column for DAY for all indicators. Even now it can be done but I have to do it carefully.
Now how to update each worsheet. Lets begin with NSE LAST THREE DAYS sheet.
Open NSE bhav copy. for this go to http://www.nseindia.com/, click on Equity just below the NSE symbol on the left hand corner, you will get a drop down menu, click on MarketToday, you will be taken to a new page, click on the link Download file in CSV format. The NSE bhav copy will open in a new page. Be patient, dont keep clicking wait for it to open with first click. Then copy all the data except the header row.
1. Next paste this data in NSE LAST THREE DAYS worksheet. Where to paste? Paste exactly below Zylog last row, from A to K.
2. For columns from L to column CS (CS is last column as per today's file, it may change later as i add more indicators), autofill from the row above which has formulae (that is zylog row of 28th August) all the way down to zylog of today so the new data gets populated with formulae in all columns. How to autofill? Click on that L cell corresponding to Zylog for daybefore i.e. Aug28th (am not sure of the cell number since i have already updated my file) and select all cells down until column CS for Zylog, click on EDIT FILL DOWN... all the cells get populated with formulae.
3. Click on EDIT SORT and sort on SYMBOL, SERIES, TIMESTAMP, in one go ascending selected. This will arrange the scrips alphabetically.
4. To be sure the other rows have no color, select all rows (except header&last row), click on the color icon on the top right of the excel toolbar and click on No Fill.
5. Next select the last row alone and impart a color of your choice again by clicking on the same icon.
6. Apply filter (by clicking on the top left corner above row1 or use CTRL&A). The whole sheet gets selected. Click on DATA FILTER AUTOFILTER.
7. Begining from column L in the filtered state, check for entries like VALUE#, DIV#0, FALSE errors, if present select them and delete them by pressing delete button on your keyboard. Take care to see that you dont delete the errors if present in the last row which is colored. If it comes up during your filter just dont delete it coz the last row is used to autofill and populate the next day's bhav.Yes this deletion of errors is done for all columns from L to CS....
8. Next select all data from column L to column CS in all rows excepting last row, click on EDIT COPY, next click on EDIT PASTE SPECIAL and select VALUES. By doing this you will be deleting all the formulae in all rows except last so the file is less bulky and works fast.
9. Next you have to delete all data older than three days. To do this select CP to CS in the last row, right click, copy, click on CP2 and paste.
10. Select CP2 to CS last row.
11. Click on EDIT FILL DOWN as explained. This will ensure that only last three days data have FILTER entry in these columns. If the columns get a orange color coz of this exercise just remove it by clicking on that color icon and selecting NO FILL. Once again color the last row afresh. Again remove all formulae in all rows for CP to CS ( except the last row) by copying, paste special, values.
12. Next filter on last column and select CUSTOM, for FL equals leave it blank as it is. Click OK. This will filter data which is older than three days and which has no *FILTER* entry in last column. Next click on EDIT and select Delete in the drop down menu (dont press delete of keyboard for this). This will delete all the older rows so you have only the last three days data.
13. Remove filter by clicking on DATA FILTER AUTOFLILTER , that will remove the check mark if present.
You are done. The whole exercise takes 10 mins but i had to write for one hour for the rank novice in excel. LOL
Unquote radhika_nandlal
Those of you still interested in my complex excel file should learn how to update. The following exercise though looks intimidating is a breeze, keep trying and practice.. it takes just 10 minutes actually though i had to write a story. LOL.. you should practice with the file you downloaded with 28th data coz u have to add 29th data... the present file in the site is already updated so that wont help for ur practice session.
All worksheets have formulae only in the last row as we only autofill from the last row down. No point in keeping formulae in the other rows as we only want the values, so i have deleted all formulae in all other rows to make it less bulky. All worksheets preferably also have windows freeze pane option chosen, if not do it yourself, Click on C2 or B2 and then click on Windows Freeze panes so when u scroll u can still look at the header and the scrip/date ur referring to.
***Remember this major flaw in excel: in filtered state if you copy and paste with one click the filtered data gets copied correctly.. but if you click paste twice with the same clipboard then all the data gets copied and you will wonder why when all you wanted was filtered data. If this happens to you, delete the pasted data, once again go to the file from which you have to copy in the filtered state, copy fresh and paste it in the new place with a single click.****
There are many COUNT columns, for any given day if its BUY in the previous column the next column COUNT gets 1 point, if its SELL it gets -1. There are many DAY columns you might wonder why. Its because I began tracking the indicators on different days. Had I tracked them all together, I could have maintained only one column for DAY for all indicators. Even now it can be done but I have to do it carefully.
Now how to update each worsheet. Lets begin with NSE LAST THREE DAYS sheet.
Open NSE bhav copy. for this go to http://www.nseindia.com/, click on Equity just below the NSE symbol on the left hand corner, you will get a drop down menu, click on MarketToday, you will be taken to a new page, click on the link Download file in CSV format. The NSE bhav copy will open in a new page. Be patient, dont keep clicking wait for it to open with first click. Then copy all the data except the header row.
1. Next paste this data in NSE LAST THREE DAYS worksheet. Where to paste? Paste exactly below Zylog last row, from A to K.
2. For columns from L to column CS (CS is last column as per today's file, it may change later as i add more indicators), autofill from the row above which has formulae (that is zylog row of 28th August) all the way down to zylog of today so the new data gets populated with formulae in all columns. How to autofill? Click on that L cell corresponding to Zylog for daybefore i.e. Aug28th (am not sure of the cell number since i have already updated my file) and select all cells down until column CS for Zylog, click on EDIT FILL DOWN... all the cells get populated with formulae.
3. Click on EDIT SORT and sort on SYMBOL, SERIES, TIMESTAMP, in one go ascending selected. This will arrange the scrips alphabetically.
4. To be sure the other rows have no color, select all rows (except header&last row), click on the color icon on the top right of the excel toolbar and click on No Fill.
5. Next select the last row alone and impart a color of your choice again by clicking on the same icon.
6. Apply filter (by clicking on the top left corner above row1 or use CTRL&A). The whole sheet gets selected. Click on DATA FILTER AUTOFILTER.
7. Begining from column L in the filtered state, check for entries like VALUE#, DIV#0, FALSE errors, if present select them and delete them by pressing delete button on your keyboard. Take care to see that you dont delete the errors if present in the last row which is colored. If it comes up during your filter just dont delete it coz the last row is used to autofill and populate the next day's bhav.Yes this deletion of errors is done for all columns from L to CS....
8. Next select all data from column L to column CS in all rows excepting last row, click on EDIT COPY, next click on EDIT PASTE SPECIAL and select VALUES. By doing this you will be deleting all the formulae in all rows except last so the file is less bulky and works fast.
9. Next you have to delete all data older than three days. To do this select CP to CS in the last row, right click, copy, click on CP2 and paste.
10. Select CP2 to CS last row.
11. Click on EDIT FILL DOWN as explained. This will ensure that only last three days data have FILTER entry in these columns. If the columns get a orange color coz of this exercise just remove it by clicking on that color icon and selecting NO FILL. Once again color the last row afresh. Again remove all formulae in all rows for CP to CS ( except the last row) by copying, paste special, values.
12. Next filter on last column and select CUSTOM, for FL equals leave it blank as it is. Click OK. This will filter data which is older than three days and which has no *FILTER* entry in last column. Next click on EDIT and select Delete in the drop down menu (dont press delete of keyboard for this). This will delete all the older rows so you have only the last three days data.
13. Remove filter by clicking on DATA FILTER AUTOFLILTER , that will remove the check mark if present.
You are done. The whole exercise takes 10 mins but i had to write for one hour for the rank novice in excel. LOL
Unquote radhika_nandlal
Saturday, September 22, 2007
Revisiting the DM formula for the ADX file
Guruprasad AKA Tapasvi of MMB has given a better formula than what i have for the ADX file column M.
In place of my formula in column M, insert the following formula in M3 yes M3
=IF(L3=1,0,IF(AND(D3=D2,E3=E2),0,IF(E3>D2,F3-D2,IF(D3<E2,F3-E2,IF(AND(D3>D2,OR(D3-D2>ABS(E3-E2),E3>E2)),D3-D2,IF(AND(D3-D2<>ABS(E3-E2),E2<E3,OR(D3>D2,E3<E2)),E3-E2,IF(E2>E3,E3-E2,0)))))))
Autofill to the last relevant cell all the way down.
This should take care of gap up and gap down opening as well.
Delete column N completely.
This formula calculates the directional movement as explained in Alexander Elder's TRADING FOR A LIVING.
In place of my formula in column M, insert the following formula in M3 yes M3
=IF(L3=1,0,IF(AND(D3=D2,E3=E2),0,IF(E3>D2,F3-D2,IF(D3<E2,F3-E2,IF(AND(D3>D2,OR(D3-D2>ABS(E3-E2),E3>E2)),D3-D2,IF(AND(D3-D2<>ABS(E3-E2),E2<E3,OR(D3>D2,E3<E2)),E3-E2,IF(E2>E3,E3-E2,0)))))))
Autofill to the last relevant cell all the way down.
This should take care of gap up and gap down opening as well.
Delete column N completely.
This formula calculates the directional movement as explained in Alexander Elder's TRADING FOR A LIVING.
Tuesday, September 18, 2007
A Handy tip to undo Errors while adding data
While adding data, it may so happen that you did not autofill before a SORT AND whats worse you also ended up saving the file with the error!! If this happens there is a way out. RELAX.
Just filter on that day's date and delete the rows by clicking on EDIT DELETE, you are back to your original sheet.
There is always a way out in excel, dont panic. LOL
Just filter on that day's date and delete the rows by clicking on EDIT DELETE, you are back to your original sheet.
There is always a way out in excel, dont panic. LOL
RSI NOTES
Given below is the explanation for RSI formula from TRADING FOR A LIVING by Alexander Elder.
I am not sure if my calculations are right. Kindly point out errors by posting a comment and lets rectify the errors.
RSI = 100 - (100 DIVIDED BY 1 + RS)
RS = Average of net UP closing changes for a selected number of days DIVIDED BY Average of net DOWN closing changes for the same number of days.
1. Obtain closing prices for the past 7 days.
2. Find all days where the price closed higher than the day before and add up the amounts of increases. Divide the sum by 7 to obtain the average UP closing change
3. Find all the days when the market closed lower than the day before and add up the amount of declines. Divide the sum by 7 to obtain the average DOWN closing change.
4. Divide the average UP closing change by the average DOWN closing change to obtain the Relative Strength (RS). Insert RS in the formula above to arrive at RSI i.e RELATIVE STRENGTH INDEX.
5. Repeat the process daily.
I am not sure if my calculations are right. Kindly point out errors by posting a comment and lets rectify the errors.
RSI = 100 - (100 DIVIDED BY 1 + RS)
RS = Average of net UP closing changes for a selected number of days DIVIDED BY Average of net DOWN closing changes for the same number of days.
1. Obtain closing prices for the past 7 days.
2. Find all days where the price closed higher than the day before and add up the amounts of increases. Divide the sum by 7 to obtain the average UP closing change
3. Find all the days when the market closed lower than the day before and add up the amount of declines. Divide the sum by 7 to obtain the average DOWN closing change.
4. Divide the average UP closing change by the average DOWN closing change to obtain the Relative Strength (RS). Insert RS in the formula above to arrive at RSI i.e RELATIVE STRENGTH INDEX.
5. Repeat the process daily.
Subscribe to:
Posts (Atom)