RSI (relative strength index) in VBA

**losmi8** · 02-08-2010, 04:38 AM

Hi all. I am wondering if anyone has the knowledge here to program RSI via VBA in Excel? I am able to do it without VBA, but it is time consuming and it takes up a lot of space and it just isn't it. Here is the description of what RSI is and the formula needed to calculate it:

http://stockcharts.com/school/doku.p...ngth_index_rsi

Can anyone do this? If so, please give me some pointers as to how to start. I have no clue how to program the "average gain" ...I am guessing it is a loop but how does one do it? Seems so complicated!

**Marcol** · 02-08-2010, 05:36 AM

On the face of it it dosn't seem difficult in VBa

The possibility of divide by 0 and what to do when this occurrs might be a problem in theory but perhaps not in practice.

Fitting to a workbook ie How and Where to get/display results is, to me at least, the biggest problem.

Could you post a non-sensitive sample workbook with say 56 rows of data?

P.S.
Just noticed

Note: If AvgLoss is zero, a "divide by zero" situation occurs for "RS" and RSI is set to 100 by definition.

**losmi8** · 02-08-2010, 05:54 AM

Marcol, here is a spreadsheet with 200 rows of data. Please keep me updated, it would be amazing if you can help me with this!

**losmi8** · 02-08-2010, 05:56 AM

Placing result, I guess it would make most sense to just run them down J column (or any other). As for each day (row), the RSI changes and the VBA would spit it out in the correct row for that day.

**Marcol** · 02-08-2010, 06:00 AM

I'm on the case look back later today!

**losmi8** · 02-08-2010, 06:15 AM

Awesome! Will do! Thank you!

**Marcol** · 02-08-2010, 09:01 AM

I rebuilt your sample to get a better understanding of the task and it occurred to me that we might be trying to re-invent the wheel!

If your data is sorted by date ascending instead of descending the whole problem might best be solved w/o VBa.
Because your Data is formula dependant a straight sort is not possible and I have fixed the formulas manually.

I then added the data as per the sample provided through the link you supplied.

Attached is the resulting spreadsheet. (Removed for further work 09 Feb)

Unless I am missing something I can't see any reason for needing VBa.

Try adding your closing data in the next free line in Sheet("SortDateAsc") and let me know what you think.

However if you definitly want a VBa solution I will be happy to help.

If this suits your needs please let me know and I wiil take the project to its conclusion for you.

Regards
Alistair

P.S. Is it the RSI sampling range you want to vary?

**losmi8** · 02-08-2010, 04:39 PM

wow! that looks good! the only reason why i would like it in VBA is because the rest of my code is in vba and i try not to have any functions on the sheet itself...and also, i'd like to add a textbox with which i could define the period of the RSI (14, or 21, etc.. by default it is 14)

if the VBA is too hard, is there any way to do what you did, but without rearranging the order of the dates? Just to leave them in descending order and have what you did would be very good as well if VBA is not an option.

let me know and thank you SO much! this is really good stuff!

**Marcol** · 02-08-2010, 07:30 PM

Okay,

It's dark o'clock on this side of the pond, I think we are about 6hrs ahead of you, so I can't do much more tonight.

My first reaction is that it would be so much easier to work by date ascending, ie chronological order, but if it can't be, so be it ........ let me sleep on it.

Sampling over 14, 21, 28 days, etc. is not a problem, I have already worked this out.

However your suggested source for information suggests a larger sampling ie 250+ to get a more accurate result. What is the implication of this?
I'm not familiar with finiancial jargon, can you help me on this?

If you can reply in the next hour or so, I could possibly have a solution early morning your time.

Regards
Alistair

**losmi8** · 02-08-2010, 08:40 PM

The only reason I would need it in descending order is because that is how my current code downloads the information into Excel. I've programed the Excel to download all the data that you saw with one easy click. And I have a lot of stuff programmed there as well, based on that descending order.

So, VBA is not a must, although it would be very helpful. But if you can help me do it in descending order, with the formula you have already used...that would be pretty amazing as well!

200 or 250, the difference will be very minimal. I have about 4,000 rows of data, but I do not know how to program it. Once you walk me through programming 200 rows, I can't see it being hard to do the other 3,800 the same way. I hope I'm not mistaken.

Thank you again!

**losmi8** · 02-09-2010, 08:55 PM

Marcol,

Here is what the spreadsheet looks like after I run the code. It imports all of the data you see in Book1.xls.

**Marcol** · 02-10-2010, 03:44 PM

Hi losmi8,

The attached workbook I hope does what you want, and perhaps a little more.

Sorry it took so long.

Try it to see what you think.

If all is well, please mark your post [Solved] and consider using the "Scales" icon top right to rate any post you have found helpful.

However if you have further queries please feel free to ask before ending the thread.

Regards
Alistair

P.S.
For some reason i can't attach the workbook!
I'll try later.

P.P.S.
attachment uploaded - (for some reason the 2003 version would not upload)

********* File Attachment Replaced 14 Feb 2010 ******
Errors in given formulae corrected and code simplified.
Check sheet using Excel Formulae added (step by step as proof for Vba)
Comments Added to code for OPs' benefit

**losmi8** · 02-10-2010, 06:17 PM

Wow! It works wonders! Nicely done Marcol!!

**Marcol** · 02-10-2010, 10:43 PM

Hi losmi8,

Success at last!!! ....

Sorry it took so long.

I still don't know why i can't post the 2003 version. ???

If all is well, please mark your post [Solved] and consider using the "Scales" icon top right to rate any post you have found helpful.
However if you have further queries please feel free to ask before ending the thread.

Regards
Alistair

**Lyra** · 07-01-2012, 05:03 AM

Here's an Excel spreadsheet that

i) automatically downloads stock quotes from Yahoo Finance. Give it a ticker symbol and two dates
ii) calculates and plots the Relative Strength Index

Everything is automated in VBA. Hope you like it

Excel spreadsheet for Relative Strength Index

**AB33** · 07-01-2012, 06:11 AM

Lyra,
Thanks for the info!
The second excel spreadhseet which has macro on it is protected with a password. I am unable to see the code. Do you happen to know the password?

**spursrule68** · 07-01-2012, 06:25 AM

Awesome!. Can you change it so it works for the London Stock Exchange as well?

**Snakepit** · 02-26-2014, 09:37 AM

In all these examples you work with a static range. What if the data is not static and keeps changing? A specific cell is being updated continuously as price changes. That price change happens, for example, every second. You must now determine the rsi value from the value in that cell. I need the value for a 14 period rsi on a 5 min chart.

**arlu1201** · 02-28-2014, 02:07 AM

Snakepit,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**Snakepit** · 03-02-2014, 10:05 AM

Sorry admins. I didn't want to create a duplicate RSI thread and clutter up the forum with two of the same items.

I will create another thread as requested.

**Mamadsafa** · 03-22-2014, 04:37 PM

Hi all. I am wondering if anyone has the knowledge here to Writing codes for RSI via VBA in Excel?
I don`t Know a lot about VBA but needs to write VBA codes for RSI.
I want to do this in attached file.

thanks a lot.....

RSI (relative strength index) in VBA

LinkBack

Thread Tools

Rate This Thread

Display

RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA, possible?

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Re: RSI (relative strength index) in VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions