Why isn't there a User Defined Function to calculate FIFO?

[Excelanil]

Hi:

I'm curious as to why hasn't anyone created an "open" Excel UDF to calculate FIFO as it relates to Stocks and Mutual Funds.

Don't get me wrong, there is one available. I found it on the Web --a template; but trying to adopt it for use is a nightmare of an an experience because the VBA code is protected; so you can't add any other VBA code or Macros. Or, if your existing Workbook/Sheet contains any VBA code or Macro, it is not possible to pull the template in for use.

The thing works well if it is used standalone; but the standalone does not conform to how most people track buying and selling and accounting for other things like: Interest Earned, Dividends Earned, Cash In, Cash Out; etc. in a spreadsheet.

I've been struggling with the available template I downloaded and have had nothing but problems in getting it to work in my situation. Most of the times I ended up with a lot of #VALUE errors in the column where the FIFO calculations are done.

Any ideas or anyone shedding light of this topic may ease my frustration.

Thank to all for supporting this forum. It is a great place to get answers.

Cheers!

 

[Excelanil]

Hi Andrew:

Thanks a million. You are a Champ.

That seems to be the trick. It works. The calculations are correct, both for stocks and mutual funds with fraction of shares.

Now, my only other problem, and I hope the last one to get resolved:

Situation:

I currently have 8 sheets in my workbook, four (4) of which are now using the FIFO UDF. Later today or tomorrow, I plan to convert the other four (4) sheets, making a total of 8 sheets that will be using the FIFO UDF.

Sometimes, when I open up the Workbook, I notice that the FIFO UDF calculations (the ones we just solved) that are supposed to be in the FIFO column are not there --the cells are empty; but if I pull down the formula, starting from the top, they appear as they should.

I'm not sure if this has something to do with my Excel setup or not.

If you can think of something that might cause the disappearance of those numbers, do let me know. Below is when it is happening:

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=187><COLGROUP><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 43pt; HEIGHT: 16.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=22 width=57></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=52></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=78></TD></TR><TR style="HEIGHT: 54pt" height=72><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #002060 1.5pt solid; WIDTH: 43pt; FONT-FAMILY: Tahoma; BACKGROUND: #dbe5f1; HEIGHT: 54pt; COLOR: navy; FONT-SIZE: 8pt; BORDER-TOP: #002060 1.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #002060 0.5pt dashed; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl95 height=72 width=57>Products</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #002060 0.5pt dashed; WIDTH: 39pt; FONT-FAMILY: Tahoma; BACKGROUND: #dbe5f1; COLOR: navy; FONT-SIZE: 8pt; BORDER-TOP: #002060 1.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #002060 0.5pt dashed; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl96 width=52>Sales</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #002060 0.5pt dashed; WIDTH: 59pt; FONT-FAMILY: Tahoma; BACKGROUND: #dbe5f1; COLOR: navy; FONT-SIZE: 8pt; BORDER-TOP: #002060 1.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #002060 1.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl97 width=78>FIFO</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 16.5pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl98 height=22 width=57>FASIX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl99 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl100 width=78></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl98 height=20 width=57>FRIFX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl99 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl100 width=78></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl98 height=20 width=57>FFVFX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl99 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl100 width=78></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl98 height=20 width=57>OAKIX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl99 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl100 width=78></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl98 height=20 width=57>YACKX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl99 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl100 width=78></TD></TR></TBODY></TABLE>

Notice, nothing in the FIFO column

Now, if I grab the lower Right and pull down, I get the numbers back like this:

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=187><COLGROUP><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 54pt" height=72><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #002060 1.5pt solid; WIDTH: 43pt; FONT-FAMILY: Tahoma; BACKGROUND: #dbe5f1; HEIGHT: 54pt; COLOR: navy; FONT-SIZE: 8pt; BORDER-TOP: #002060 1.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #002060 0.5pt dashed; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl96 height=72 width=57>Products</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #002060 0.5pt dashed; WIDTH: 39pt; FONT-FAMILY: Tahoma; BACKGROUND: #dbe5f1; COLOR: navy; FONT-SIZE: 8pt; BORDER-TOP: #002060 1.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #002060 0.5pt dashed; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl97 width=52>Sales</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #002060 0.5pt dashed; WIDTH: 59pt; FONT-FAMILY: Tahoma; BACKGROUND: #dbe5f1; COLOR: navy; FONT-SIZE: 8pt; BORDER-TOP: #002060 1.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #002060 1.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl98 width=78>FIFO</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 16.5pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl99 height=22 width=57>FASIX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl100 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl101 width=78>20,000.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl99 height=20 width=57>FRIFX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl100 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl101 width=78>15,000.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl99 height=20 width=57>FFVFX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl100 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl101 width=78>20,000.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl99 height=20 width=57>OAKIX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl100 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl101 width=78>18,000.01</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 43pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl99 height=20 width=57>YACKX</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl100 width=52></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl101 width=78>8,813.95</TD></TR></TBODY></TABLE>


By the way, this same sort of problem happens if I use the original author'd download with some of my data.

Once again, thanks for all your help.

 

[Excelanil]

Hi Andrew:

Further to my last post, this may give you a clue.

If I go to an empty cell and say I enter =BL9, where BL9 is the cell of the first FIFO UDF calculations, the numbers re-appear as they should. Also, If I say =BL10, or +BL11, etc. the numbers re-appear.

Interesting!

Cheers!

 

[Andrew Poulsom]

Sorry, I have no idea why your numbers are disappearing.

 

[Excelanil]

Hmmmm!

It's a puzzle, alright.

I'll do some more testing, checking, etc. and will let you know.

Cheers!

 

[alexcn]

First a question. How can you sell 195 of WITS on 10 April 2006 when you only have 100 (purchased on the same day)?

I had to add something here, of course you can sell 195 units when you only have 100, it is called "shorting" the stock and bets on the price of the underlying equity going down, in this case your "position" would be -95 so you would need (at some point) to buy back 95 lots to be flat, hopefully at a lower price than you sold them to realise a profit.