• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

XIRR In Access Issue

I have written a code and Access that seemed to work at first, but after I ran it once, I seem to be getting the same answer over and over again, regardless of whether or not I change the information in the record set.  Am I missing something here?
Code is posted below.  Thanks.

Private Sub Command0_Click()
Dim dbs As Database
Dim rstXIRR As DAO.Recordset
Dim i As Integer
Dim nPayments As Integer
Dim objExcel As Excel.Application
Dim p() As Double
Dim d() As Date


Set dbs = CurrentDb
Set rstXIRR = dbs.OpenRecordset("tblTransactions", dbOpenDynaset)

nPayments = DCount("PayDate", "tblTransactions")
ReDim p(nPayments)
ReDim d(nPayments)

i = 0
With rstXIRR
While Not .EOF
  p(i) = !Payments
  d(i) = !PayDate
  i = i + 1
  .MoveNext
Wend
End With

Set objExcel = New Excel.Application
objExcel.RegisterXLL objExcel.Application.LibraryPath & "\ANALYSIS\ANALYS32.XLL"
MsgBox objExcel.Run("XIrr", p, d)  ' Result:  0.374858599901199
objExcel.Quit
Set objExcel = Nothing

End Sub

Open in new window

0
tgilbride
Asked:
tgilbride
  • 6
  • 4
1 Solution
 
GRayLCommented:
Shouldn't the Wend and End With be between 30 AND 31
0
 
tgilbrideAuthor Commented:
That does not seem to work

Is there anything else I might be missing?

Perhaps deleting the RecordSet or something?
0
 
Rory ArchibaldCommented:
You could try adding:
rstXIRR.Close
to the end of the code?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tgilbrideAuthor Commented:
That did not work either.

Any other ideas?
0
 
Rory ArchibaldCommented:
Have you stepped through the code to check the recordset actually has the proper values? There is nothing wrong with the Excel part that I can see.
0
 
tgilbrideAuthor Commented:
Everything seems to be fine from that aspect.
0
 
Rory ArchibaldCommented:
Have you tried outputting the data to Excel so you can check if the XIRR results are actually different?
0
 
tgilbrideAuthor Commented:
I have cut and pasted the data to excel and the results are much different.

But I keep getting the same result from the first recordset.

Is there a way to use the first part of the code (the array) and put that directly into excel so I can further analyze the record set?
0
 
Rory ArchibaldCommented:
Yes - you can either use copyfromrecordset to drop the data straight into Excel, or you can just use the arrays - see below.

I note your arrays actually have one more item than required. Does it help if you use:

nPayments = DCount("PayDate", "tblTransactions")
ReDim p(nPayments - 1)
ReDim d(nPayments - 1)


objExcel.Activesheet.Range("A1").Copyfromrecordset rstXIRR

or:

objExcel.Activesheet.Range("A1").Resize(nPayments).Value = objExcel.Transpose(p())

Open in new window

0
 
tgilbrideAuthor Commented:
OK, I think I  finally got it.  I was able to change input the information into excel and the reason I keep getting the wrong answer is that the data is not sorted in ascending order by data.  Can you take my original code and tell me how to sort the record set by "PayDate"?

Thanks
0
 
tgilbrideAuthor Commented:
Bottom line is that I needed to see how the array was being viewed by in order to fix the issue.  Thanks for your help.  
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now