In: Computer Science
VBA question, write a function check keyword in a string.
Question:
Function sentimentCalc(tweet As String) As Integer
This function should check each word in the tweet and if the word exists as one of the keywords in the positive list or negative list it should impact the overall sentiment value. The positive list and negative list words exist in the keywords sheet. Access the keywords as ranges within your VBA code. The case of the word is inconsequential. For instance, happy, HAPPY, or hApPy are all treated as positive words regardless of their case (Hint: StrComp).
We have a keyword excel show that which word is positive and which is negative. How to get the keyword please see the hit part below.
If the word is in the positive list, it should increase the sentiment value by 10, if it is in the negative list it should decrease it by 10.
For instance, if the positive list includes “happy”, “rally”, “growth” and the negative list includes “crash”, “scam”, “bad” then:
If the Tweet is “I am Happy that Bitcoin is showing growth.”. The sentiment value will be 10 + 10 = 20
If the Tweet is “I am happy that Bitcoin is a scam and will CRASH!” The sentiment value will be 10 – 10 – 10 = -10
You must remove the following punctuation characters from the tweet text in your VBA code before calculating the sentiment: ! . , ? : ) ( ;
You may do this using multiple lines each calling the Replace function or with an array, loop and one call to the Replace function. Both methods will be marked as correct.
HIT:
You will need to use the string functions StrCom p, Split and Replace In this function.To get the ranges from the keywords Sheet use Worksheet and Range object like so:
Dim positive As Range
Set positive = Worksheets("keywords").Range("A2:A76")
Dim negative As Range
Set negative = Worksheets("keywords").Range("B2:B76")
This will give you the range A2:A76.From the sheet namedkeywords As the variable named positive.You can do the same for the negative range (but with different cell references And variable names).
You will need to use nested loops. One to go through each word in the keywords and one to Go through each word in the tweet text.
Use this function in your processedData worksheet to calculate the sentiment value for each
tweet (store in Column E)
Create a function with the header:
Function sentimentCategory(sentVal As Integer) As String
o if the sentiment value is greater than 0, the Sentiment Category is “Positive”
o If the sentiment value is less than 0, its category is “Negative”.
o If the sentiment value is equal to 0, its category is “Neutral”.
Use the above function to determine the category for each tweet (store in Column F)
3. Descriptive Analysis Next you will analyze the sentiment of the tweets. You will use built-in excel functions (not VBA).
For most of the tasks below there are multiple ways to do it, two of which are described below.
https://support.office.com/en-us/article/Paste-values-not-formulas-12687b4d-c79f-4137-b0cc-947c229c55b9
The easiest way (but not the most effective) is to manually determine the range for each topic and use
this range inside excel functions (your data must be sorted first by topic and then by group to do this).
Another alternative is to use the AVERAGEIF(S)/COUNTIF(S) function and create a separate column with
only one instance of each topic/group.
First create a new worksheet called analyzedData. A screen shot of what the layout of this worksheet
should look like is shown below in Figure 1.
TASK A: Calculate the overall sentiment value for each topic.
o Overall Sentiment for Topic: average of the sentiment values for the tweets within that
topic (There are over 39 topics in the dataset)
TASK B: Calculate the overall sentiment value for each group.
o Overall Sentiment for Group: average of the sentiment values for the tweets within each
group (There are about 8 groups in the dataset)
TASK C: Determine the total number of Positive, Negative, and Neutral Tweets in the dataset
TASK D: Determine the number of Positive, Negative and Neutral Tweets for each group
TASK E: Determine the number of Positive, Negative and Neutral Tweets for each topic