course analysis from bitcoins with VBA

[Sharism]

Hello,

I thought you can help because I am totally new to VBA. So I must do the following steps but dont know how to start:




Create a VBA function, which should load the price development BTC/USD) daily from QUANDL in the excel worksheet, you can use the JSON, CSV or XML format.
After the data has been loaded, you should calculate in an extra column the simple moving average (SMA).
And in an extra column you should calculate the Relative Strength Index (RSI).



Any help would be a great offer. Thanks in advance. :)

[SamT]

Please attach a workbook with the worksheet with the QUANDL data and a worksheet with a sample of the desired results.

Use the Go Advanced Option and below the post editor, will be a Manage Attachments button.

[Sharism]

Hello,

the problem is that I don´t have a worksheet with a sample of the desired results. I can give you only the csv file, which you could also download on QUANDL :D

I need a VBA code which should load the data from QUANDL into a worksheet, as I write above.

Thanks

[Paul_Hossler]

Loading the CSV file is easy but what are the formulas (EXACTLY!!) for these?


After the data has been loaded, you should calculate in an extra column the simple moving average (SMA).
And in an extra column you should calculate the Relative Strength Index (RSI).


Which columns, how any days in the past, etc.??

[Sharism]

Hello,

ah sorry I forgot to mention that.

Both calculation should be 21 days. The teacher said we should use the LAST price, because this is more important as the other prices.

[SamT]

The teacher said
We don't do homework. We will help you figure out how to do it yourself and we will help you correct any errors in your code.

So you need code that will, from Excel:


Download a report (csv file) from QUANDL, (whatever that is.an internet search did not return any results in a reasonable time.)

Open the report in Excel for further analysis.

Calculate the SMI

Calculate the RSI.



First, we're VBA Geeks, we have no clue as to what SMI and RSI are or how to calculate them.


Learning to code means learning to think thru each required step. If we are to help you, you must list each required step. Create this list for us and review it over and over until you are certain that you have not missed a step in the process and calculations.

Be specific and speak/write in terms of Excel, not BitCoins or finances. If you mean the last 21 days, tell us "The top 21 Rows of the Sheet." If you means Dates, tell us "Column "A", The Dates Column."

If describing a formula or calculation, tell us something like, "The average of the 21 first cells in Column "X", divided by (the Maximum value therein minus the minimum value therein)."

This type of practice will be required when you start using VBA yourself. By then you will be thinking in terms of VBA and of Excel. For now, think in terms of Excel and we will translate into VBA.

[Paul_Hossler]

Many time the macro recorder will get you started. It doesn't do the best or most efficient code since it only captures every user action (click, scroll, etc.)

I use it a lot of time to capture a step in a larger macro

This is just the macro recorder capturing my actions. It is NOT suitable as a final product since there are a lot on unneeded .Select actions that are not required since the action should be applied directly to the object, and all references are hard coded.




Option Explicit
Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\userid\Desktop\BITFINEX-BTCUSD (3).csv", Destination:=Range( _
"$A$1"))
.CommandType = 0
.Name = "BITFINEX-BTCUSD (3)"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("I1").Select
ActiveCell.FormulaR1C1 = "Moving Average"
Range("J1").Select
ActiveCell.FormulaR1C1 = "RSI"
Range("I22").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-20]C[-4]:RC[-4])"
Range("I22").Select
Selection.AutoFill Destination:=Range("I22:I746")
Range("I22:I746").Select
Range("J22").Select
ActiveCell.FormulaR1C1 = "=100*RC[-1]"
Range("J22").Select
Selection.AutoFill Destination:=Range("J22:J746")
Range("J22:J746").Select
End Sub




Follow SamT's advice and think 'Excel' not 'Finance'

Feel free to ask questions if you get stuck

[Aussiebear]

This is a great example of coding based on the Macro recorder verses the actual needs of the OP. Showing us an example early in the construct would save consideable discourse later on.

[SamT]

Student,

Also see https://msdn.microsoft.com/en-us/library/office/aa195811%28v=office.11%29.aspx
and https://msdn.microsoft.com/en-us/library/office/ff194819.aspx

[Sharism]

Okay guys,

I got the two calculation with the macro fuction.

Paul when I use your macro, I get an error and "CommandType = 0" is yellow marked. Why?


Can I also import the data for example from a .csv file without the macro functon? If yes, how?

Thanks a lot

[Paul_Hossler]

Okay guys,

I got the two calculation with the macro fuction.

Paul when I use your macro, I get an error and "CommandType = 0" is yellow marked. Why?

Can I also import the data for example from a .csv file without the macro functon? If yes, how?

Thanks a lot

I'm using Excel 2016

I merely recorded a macro with the csv file in a folder on my PC as an example of using the macro recorder as a starting point

My suggestion was to record your macro as a starting point, and then customize and generalize the result (that's if not the best, at least a good way to learn)

I had a blank WB open and ran the macro

I don't know why the error line, but I assume you changed "C:\Users\userid\Desktop\BITFINEX-BTCUSD (3).csv" at least to where your file is

You can read the CSV by double clicking it to open in Excel, or with another WB open, use [Data], [From Text] to select the csv file

[p45cal]

I get an error and "CommandType = 0" is yellow marked. Why?[/COLOR]
When I tried on Excel 2010, it also failed at this line; in the help file it only gives possible values of 1 to 5 (no 0). Looking at the property in the locals pane it says <This option is unavailable for this type of external database.> So I guess a change from Excel 2010 to Excel 2016. Just remove that line altogether.

Be aware that the data is sorted by date with the most recent at the top so the simple moving average formula will need a tweak or the data sorted in the opposite direction.




Can I also import the data for example from a .csv file without the macro functon? If yes, how?On a fresh sheet, on the Get exrternal Data section of the Data Tab of the ribbon, click the From Text icon, select your .csv file and click Import.
Text Import Wizard Step 1: choose Delimited
Text Import Wizard Step 2: choose only Comma from the delimiters,
Text Import Wizard Step 3: click Finish (I'm not sure here, you just might have to tweak this step by choosing YMD for the 1st column's data format.)
Choose where you want the data to be dumped, OK.

[Paul_Hossler]

Okay guys,
Paul when I use your macro, I get an error and "CommandType = 0" is yellow marked. Why?

I'm using 2016 and there are some new features apparently

If you record your own macro, the results will be compatible with your version and you'll be able to see how to approach it

My macro loaded the csv file, added two columns, and put some formulas in and flowed them down

If you do the same with a recorded macro, it should help you with your homework

[snb]

Crossposted:

http://www.office-loesung.de/p/viewtopic.php?f=166&t=717727&p=2973224#p2973126

[Sharism]

Hello,

p45cal thanks for your reply. I mean without the macro record function, but the way with the Data Tab would be too easy haha.

I imported the .csv file with the following code:



Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" ( _
ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Sub download_und_import_bitcoin_courses()
If download_file <> 0 Then
MsgBox "Problem beim herunterladen.", vbExclamation
Exit Sub
End If

Call import

MsgBox "Import erfolgreich.", vbInformation
End Sub

Private Function download_file() As Long
Dim strURL As String
Dim strLocalFile As String

'Link zum Donwload
strURL = "here is the link to the .csv file, but I can´t paste it"

'Pfad für den Speicherort
strLocalFile = ThisWorkbook.Path & "\Bitcoin_" & Format(Date, "YYYYMMDD") & ".csv"

'Datei herunterladen und Status zurückgeben
download_file = URLDownloadToFile(0, strURL, strLocalFile, 0, 0)
End Function

Private Sub import()
Dim fso As Object
Dim txtStream As Object
Dim i As Integer, j As Integer
Dim strPfad As String
Dim strDaten() As String
Dim wksImport As Worksheet
'Tabelle, in der importiert wird
Set wksImport = Worksheets("Tabelle1")
'Bereich in dem Eingefügt wird (1,1 = A1; 2,1 = A2..)
i = 1: j = 1
'Tabellenblatt leeren
wksImport.Cells.Clear

strPfad = ThisWorkbook.Path & "\Bitcoin_" & Format(Date, "YYYYMMDD") & ".csv"
Set fso = CreateObject("Scripting.FilesystemObject")
Set txtStream = fso.OpenTextfile(strPfad)

Do While Not txtStream.AtEndOfStream
strDaten() = Split(txtStream.ReadLine, ",")
For j = 0 To UBound(strDaten())
wksImport.Cells(i, j + 1) = strDaten(j)
Next j
i = i + 1
Loop

txtStream.Close
Set txtStream = Nothing
Set fso = Nothing
End Sub


Function average (n As Integer) As Double


Dim arr As Range
Dim v As Variant
Dim sum As Double

Set arr = Range("E2:E22")

For Each v In arr
sum = sum + v

Next

average = sum / n



End Function





But the Function to calculate the average, won´t work. The first row is correct, but the second, third and so on is the same. Any ideas?


Thanks a lot


kind regards!

[Paul_Hossler]

But the Function to calculate the average, won´t work. The first row is correct, but the second, third and so on is the same. Any ideas?

I assume that you're using average as a WS function and trying to fill down? =average(21)

I expect the reason the results are the same is because the inputs are the same: Set arr = Range("E2:E22")

1. Pass the averaging range as a parameter and Excel will adjust the cell references for you: =myAverage(E2:E22)
2. There is no need to pass the number of elements doing this, since the .Count property returns that
3. There is a intrinsic WS function called AVERAGE() that you can use also




Option Explicit

Function myAverage(arr As Range) As Double
Dim v As Variant
Dim mySum As Double


For Each v In arr
mySum = mySum + v
Next

myAverage = mySum / arr.Count

End Function

[Sharism]

Hello,

thanks a lot Paul. It worked with your function.


But what if I need the average of the last 500 days. I mean 21 days are easy to select, from E2 to E22, but 500??

Should I rather select 500 cells or is there a roundabout?


Thanks again Paul :D

[Paul_Hossler]

You could select 500 cells and pass that range: cell B530 would be =myAverage(A30:A530)

or you can pass the final / last / end cell (A69) and the number to use on the MA (100) and let the function handle it, returning an error if there are not enough cells

=MovingAverage(A69,100)




Option Explicit

'from and including R back N unless we go off the sheet
'assumes headers in row 1
Function MovingAverage(R As
Range, N As Long) As Variant
Dim rToAverage As Range
Dim mySum As Double
Dim i As Long

'at least N in the past
If R.Row > N + 1 Then
Set rToAverage = R.Offset(-N, 0).Resize(N, 1)

'otherwise return a #NUM!
Else
MovingAverage = CVErr(xlErrNum)
Exit Function
End If

For i = 1 To rToAverage.Cells.Count
mySum = mySum + rToAverage.Cells(i)
Next

MovingAverage = mySum / rToAverage.Cells.Count
'or
'MovingAverage = Application.Worksheetfunction.Average(rToAverage)
End Function

[Sharism]

Hello,

okay I solved it, I think. I attached the file so you can see it better.


Now I need a configuration sheet, as you can see in table 2. So when I press 21, 22 or 23,... it should calculate the average of this days in an another column.

Same for the Relative Strength Index and the BTC/USD price history, so when I press 365, it should only show the last 365 prices.

Does anyone have an idea, how to do this?


Thanks a lot. :)

[p45cal]

you don't need durchschnitt, it's built-in.
In cell A2 you can have:
=AVERAGE(OFFSET(Tabelle1!E2,0,0,$G$3))
which uses the value in G3 to set the average range extent.
You can do something similar in C2:
=RSI(OFFSET(Tabelle1!E2,0,0,$G$4))
which uses G4's value to set the exent of the RSI.

Your sorting macro may not always sort the right range (limited to A1:H750) so:
Sub sortierung()
Dim myRng As Range
Set myRng = Range("A1").CurrentRegion
With ActiveWorkbook.Worksheets("Tabelle1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange myRng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

[Sharism]

Hello,

thanks p45cal for the sub sortierung().


I tried it it with =AVERAGE(OFFSET(Tabelle1!E2,0,0,$G$3)) and it worked.


How can I use my "durchschnitt" instead of "AVERAGE"?

Because I know "AVERAGE" and it would be perfect, if there would not be my "durchschnitt" function.


And how can I do it with the 365 days? So when I type 365, there should be the last 365 courses in Table2 2/Column D from Table 1/Column E?


Thanks at all :D

[Sharism]

okay, I got it with my function

so there is just my last question left

[p45cal]

see attached.

[Sharism]

Hello p45cal,

thanks to you I am almost done. There are just a few steps.

So I have to create a chart in excel with the LAST course, the RSI, the Moving Average.




I almost got it but there is a problem with the dates in the x-Axis. Can you please take a look?
Can I use the RSI in the same chart or do I have to create a new chart just with the RSI?
I tried with the macro recorder, that the RSI disappears when I click the button, but it doesn´t work because it didn´t record anything. Why?


thanks :D

[p45cal]

In attached:
Corrected formulae for SMA and RSI so that they computed the right ranges.
Chart always includes most recent date.
Extend the formulae downwards in Tabelle2 columns A:D to accommodate larger datasets in Tabelle1 as required.



I almost got it but there is a problem with the dates in the x-Axis. Can you please take a look?
Can I use the RSI in the same chart or do I have to create a new chart just with the RSI?
I tried with the macro recorder, that the RSI disappears when I click the button, but it doesn´t work because it didn´t record anything. Why?

1. I think solved
2. Easier as it is, otherwise you will have to line up two charts exactly; a pain but do-able.
3. What steps were you taking to get the RSI to disappear?

[Sharism]

Hello p45cal,

first thanks for your help.


I have used your version as above, but why it takes so long to load the new data?

In my previous version I solved the problem, like that:


Sub zurücksetzen()

Sheets("Tabelle2").Select
Range("G3").Select
ActiveCell.FormulaR1C1 = "1"
Range("G4").Select
ActiveCell.FormulaR1C1 = "1"
Range("G5").Select
ActiveCell.FormulaR1C1 = "1"
Range("G6").Select
End Sub


Then it will import and sort the data and than this, as standard for the graphic.



Sub standardwerteSetzen()

Sheets("Tabelle2").Select
Range("G3").Select
ActiveCell.FormulaR1C1 = "21"
Range("G4").Select
ActiveCell.FormulaR1C1 = "21"
Range("G5").Select
ActiveCell.FormulaR1C1 = "365"
Range("G6").Select
End Sub


Did you change something in your version?

[p45cal]

Ah yes. It's because I used OFFSET in the formulae; it's a volatile function, so any change in the any cell in the workbook causes a recalculation of them.
There are 3 things that can be done:
1. Change the OFFSET formulae to ones which use INDEX and MATCH - may get time to look at this.
2. Turn off calculation (and perhaps screenupdating) during the import process - see below.
3. Change the way the data is imported (at the moment it seems to come in one cell at a time!).

re 2. above: don't use zurücksetzen and standardwerteSetzen
'Call zurücksetzen ' don't use.
Application.Calculation = xlManual
Sheets("Tabelle1").Select
Call import
Call sortierung
Application.Calculation = xlAutomatic
' Call standardwerteSetzen ' don't use.
or with screen updating switched off/on:
'Call zurücksetzen ' don't use.
Application.Calculation = xlManual
Application.ScreenUpdating = False
Sheets("Tabelle1").Select
Call import
Call sortierung
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
' Call standardwerteSetzen ' don't use.

[p45cal]

…and point 3 above; tweaked the import sub to put all the data on the sheet in one go. See attached.

[p45cal]

…and finally point 1 above, formulae changed to non-volatile functions. Should be an altogether more sprightly file, see attached.

[Sharism]

In version 2c

what is this:


Sub Macro1()'
' Macro1 Macro
'


'
Application.Calculation = xlManual
Range("L3").Value = "d"
Application.Calculation = xlAutomatic
Range("L3").ClearContents
Range("M3").Value = "hg"
Range("M3").ClearContents
Range("M3").Value = "sd"
Range("O3").Select
End Sub
Sub Macro2()
'
' Macro2 Macro
'


'
*** = Range("A2:H39").Value
End Sub





I mean what does it?

[p45cal]

Delete that module; macro1 was me checking when recalculations took place (every single cell change regardless of which sheet). Yes, your import routine brought the data in one cell at a time, recalculating the whole shooting match after each cell.
macro2 was me looking at the dimensions of a range variable to see how I should construct the receiving array for your data.
'Thanks' would be nice.

[Sharism]

Hello p45cal,

of course, thank you very very much.

So I can use version 2c or 2d and the solution would be the same?

[p45cal]

So I can use version 2c or 2d and the solution would be the same?Well I think so. It is up to you to check; it's easy enough to have them both open at once and test.

[offthelip]

Hi Sharism,
As well as being a VBA expert. I also know something about financial functions. Your calculation of the RSI function is incorrect. You are using a standard average of Sum/count. In Welles Wilder's original calculation the averaging is done using an exponential moving average. This means that you need to calculate the exponential average of the UPs and the Downs and then calculate the RSI from the Exponential moving average.

[Sharism]

Hello offthelip,


I don´t know what you mean. If it is false, then I would be devastated haha.

Can you correct it and show me exactly what you did? It would help me a lot.


Thanks to both of you.

[Sharism]

I have the RSI code from here:

http://www.mrexcel.com/forum/excel-questions/201709-using-visual-basic-applications-calculate-relative-strenght-index.html

and that guy said he got it right. So what is false? :D

[Sharism]

Thanks very much.

[offthelip]

whoever wrotethe code on mrexcel didn't know much about welles wilder RSI.

I suggest you read about RSI: see this web site:
http://stockcharts.com/school/doku.php?st=rsi&id=chart_school:technical_indicators:relative_strength_index_rsi
There are lots of other websites which explain RSI as well. Since this is a home work project I can't just give you the code , you need to investigate it first.

Your equation calculates the first value correctly but not any subsequent value.

You might also find it useful to read about exponential moving averages which the RSI uses for the ups and downs

http://stockcharts.com/articles/mailbag/2013/01/what-is-the-difference-between-a-simple-and-exponential-moving-average.html?st=exponential+moving+average


Best of luck.

[p45cal]

I got an email saying Sharism asked a question, but it doesn't appear here. This was what I got:
For example, the are now max. 777 datasets in Tabelle1. That means I can set in Tabelle2/LAST max. 777 days.

And when I type in 778 days, I get an error. Okay I know this.


But how can I know the "max. 777 days" without scrolling down?

Put the formula:
="Max. = " & COUNT(A:A)+1
in say, cell H5.

…and don't forget what I said in msg#25:
Extend the formulae downwards in Tabelle2 columns A:D to accommodate larger datasets in Tabelle1 as required.