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:

1. 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.
2. After the data has been loaded, you should calculate in an extra column the simple moving average (SMA).
3. 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

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:

1. Download a report (csv file) from QUANDL, (whatever that is.an internet search did not return any results in a reasonable time.)
   
2. Open the report in Excel for further analysis.
   
3. Calculate the SMI
   
4. 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/lib...ice.11%29.aspx
and https://msdn.microsoft.com/en-us/lib.../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/viewt...73224#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

[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