Excel Formulas and Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-3074537%22%20slang%3D%22en-US%22%3EExcel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074537%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20start%20I%20am%20on%20a%20Dell%20desktop%20using%20Microsoft%20365%20Excel%20MSO%20(Version%202112%20Build%2016.0.14729.20254)%2064-bit16.0.14729.20254)%2064-bit%2C%20WIN%2010.%3C%2FP%3E%3CP%3EIn%20the%20attached%20spreadsheet%20I%20input%20a%20date%20and%20get%20back%20the%20value%20of%20a%20stock%20so%20that%20I%20can%20follow%20whether%20it%20is%20going%20up%20or%20down.%26nbsp%3B%20The%20spreadsheet%20I%20pulled%20this%20from%20has%20about%2040%20different%20companies%20and%20mostly%20they%20all%20work%20except%20for%20the%20few%20I%20present%20in%20this%20example.%26nbsp%3B%20Columns%20B%20thru%20G%20are%20some%20SPAC's%20(special%20purpose%20acquisition%20company)%20that%20I%20have%20and%20want%20to%20follow.%26nbsp%3B%20Row%202%20is%20the%20stock%20symbol%20and%20the%20name%20of%20the%20company.%26nbsp%3B%20Row%204%20is%20the%20number%20of%20shares%20I%20own.%26nbsp%3B%20Warrants%20in%20SPAC's%20are%20part%20of%20the%20shares%20I%20purchase.%20%26nbsp%3BThe%20offering%20includes%20warrants%20based%20on%20some%20percentage%20of%20the%20number%20of%20shares%20purchase%20(like%201%20warrant%20for%20each%204%20shares%20purchased%20or%201%20warrant%20for%20every%202%20shares%20purchased).%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20positive%20that%20the%20symbols%20for%20the%20spacs%E2%80%99%20are%20correct%2C%20but%20that%20is%20what%20was%20reported%20to%20me.%26nbsp%3B%20At%20least%20the%20share%20or%20warrant%20reported%20back%20to%20me%20was%20the%20correct%20symbol.%3C%2FP%3E%3CP%3E%3CU%3EFirst%20problem%3C%2FU%3E.%3C%2FP%3E%3CP%3EColumn%20B%20is%20the%20company%20shares%20for%20AURORA%20Innovation%20and%20column%20C%20is%20the%20warrant%20associated%20with%20the%20same%20company.%26nbsp%3B%20You'll%20see%20in%20cell%20C2%20it%20lists%20the%20company%20as%20ARROW%20(the%20W%20stands%20for%20warrant)%20Financial%20Corp.%26nbsp%3B%20This%20is%20wrong%2C%20it%20should%20show%20Aurora%20Innovation%20warrants.%3C%2FP%3E%3CP%3E%3CU%3ENext%20problem%3C%2FU%3E.%3C%2FP%3E%3CP%3EColumn%20D%20show%20EQRX%20and%20it%20pulls%20up%20the%20shares%20for%20EQRX.%26nbsp%3B%20Column%20E%20is%20the%20warrants%20for%20EQRX%20from%20stock%20symbol%20EQRXW.%26nbsp%3B%20Here%20the%20lookup%20fails%20and%20only%20returns%20a%20value%20error.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3ELast%20problem%3C%2FU%3E.%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20F%20represents%20the%20shares%20for%20RAM%20(formerly%20known%20as%20Aries%20I%20Acquisition)%20and%20it%20works%20correctly.%26nbsp%3B%20Column%20G%2C%20just%20like%20the%20previous%20problem%2C%20returns%20an%20error.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20spreadsheet%20with%20formulas%20is%20presented%20so%20that%20you%20see%20exactly%20the%20symbols%20and%20the%20functions.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20someone%20try%20to%20help%20me%20fix%20these%20problems%20as%20I%20am%20at%20a%20complete%20loss%20on%20where%20to%20go.%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20help.%3C%2FP%3E%3CP%3ESteve%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074537%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3087655%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3087655%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055336%22%20target%3D%22_blank%22%3E%40SteveL50%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20companies%20and%20symbols%20In%20the%20sheet%20you%20attached%20do%20NOT%20correspond%20to%20your%20descriptions.%20First%20issue.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1643498911992.png%22%20style%3D%22width%3A%20667px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343613i72ACFD7D3CE4B65F%2Fimage-dimensions%2F667x127%3Fv%3Dv2%22%20width%3D%22667%22%20height%3D%22127%22%20role%3D%22button%22%20title%3D%22mathetes_0-1643498911992.png%22%20alt%3D%22mathetes_0-1643498911992.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20A%20second%20point%2C%20though%2C%20may%20be%20more%20relevant.%20I%20use%20the%20STOCKHISTORY%20function%20as%20well%2C%20and%20have%20discovered%20that%20it's%20not%20always%20reliable.%20In%20my%20case%2C%20the%20history%20for%20BRKB%20was%20off%20by%20significant%20factors.%20There's%20a%20reason%20for%20the%20%22Disclaimer%22%20that%20appears%20at%20the%20top%20of%20the%20sheet%20whenever%20you%20first%20load%20it.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1643498944137.png%22%20style%3D%22width%3A%20702px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343614i788EF8D5D4E67EEA%2Fimage-dimensions%2F702x39%3Fv%3Dv2%22%20width%3D%22702%22%20height%3D%2239%22%20role%3D%22button%22%20title%3D%22mathetes_1-1643498944137.png%22%20alt%3D%22mathetes_1-1643498944137.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20It%20wouldn't%20surprise%20me%20to%20hear%20that%20data%20concerning%20SPACs%20is%20not%20altogether%20reliable.%20The%20fact%20that%20most%20of%20your%2040%20companies%20DO%20work%20is%20evidence%20that%20you're%20doing%20this%20as%20you%20should.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20want%20to%20check%20out%20data%20on%20Yahoo%20Finance%20for%20the%20ones%20that%20don't%20work%20via%20STOCKHISTORY%3B%20that's%20what%20I%20had%20to%20do%20for%20BRKB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

To start I am on a Dell desktop using Microsoft 365 Excel MSO (Version 2112 Build 16.0.14729.20254) 64-bit16.0.14729.20254) 64-bit, WIN 10.

In the attached spreadsheet I input a date and get back the value of a stock so that I can follow whether it is going up or down.  The spreadsheet I pulled this from has about 40 different companies and mostly they all work except for the few I present in this example.  Columns B thru G are some SPAC's (special purpose acquisition company) that I have and want to follow.  Row 2 is the stock symbol and the name of the company.  Row 4 is the number of shares I own.  Warrants in SPAC's are part of the shares I purchase.  The offering includes warrants based on some percentage of the number of shares purchase (like 1 warrant for each 4 shares purchased or 1 warrant for every 2 shares purchased). 

I am not positive that the symbols for the spacs’ are correct, but that is what was reported to me.  At least the share or warrant reported back to me was the correct symbol.

First problem.

Column B is the company shares for AURORA Innovation and column C is the warrant associated with the same company.  You'll see in cell C2 it lists the company as ARROW (the W stands for warrant) Financial Corp.  This is wrong, it should show Aurora Innovation warrants.

Next problem.

Column D show EQRX and it pulls up the shares for EQRX.  Column E is the warrants for EQRX from stock symbol EQRXW.  Here the lookup fails and only returns a value error.  

Last problem

Column F represents the shares for RAM (formerly known as Aries I Acquisition) and it works correctly.  Column G, just like the previous problem, returns an error.  

The attached spreadsheet with formulas is presented so that you see exactly the symbols and the functions. 

 

Would someone try to help me fix these problems as I am at a complete loss on where to go.

Thanks in advance for your help.

Steve

1 Reply

@SteveL50 

 

1. The companies and symbols In the sheet you attached do NOT correspond to your descriptions. First issue.

mathetes_0-1643498911992.png

 

2. A second point, though, may be more relevant. I use the STOCKHISTORY function as well, and have discovered that it's not always reliable. In my case, the history for BRKB was off by significant factors. There's a reason for the "Disclaimer" that appears at the top of the sheet whenever you first load it.

mathetes_1-1643498944137.png

 

3. It wouldn't surprise me to hear that data concerning SPACs is not altogether reliable. The fact that most of your 40 companies DO work is evidence that you're doing this as you should. 

 

You might want to check out data on Yahoo Finance for the ones that don't work via STOCKHISTORY; that's what I had to do for BRKB