Question

In: Computer Science

VBA question, write a function check keyword in a string. Question: Function sentimentCalc(tweet As String) As...

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.

Solutions

Expert Solution

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


Related Solutions

(10 marks) Write a function to check whether string s1 is a substring of string s2....
Write a function to check whether string s1 is a substring of string s2. The function returns the first index in s2 if there is a match. Otherwise, return -1. For example, the function should return 2 if s1 is "to" and s2 is "October". If s1 is "andy" and s2 is "candy", then the function should return 1. The function prototype is as follows: int indexOf(const char *s1, const char *s2).
C# Palindrome Permutation: Given a string, write a function to check if it is a permutation...
C# Palindrome Permutation: Given a string, write a function to check if it is a permutation of a palindrome. A palindrome is a word or phrase that is the same forwards and backwards. A permutation is a rearrangement of letters. The palindrome does not need to be limited to just dictionary words. Input: Tact Coa Output: True (permutations: "taco cat", "atco cta", etc.) Comment your code to explain it.
Write a recursive function to check if a string whose each character is stored in a...
Write a recursive function to check if a string whose each character is stored in a separate node in a doubly linked list, is a palindrome. Use the code available from DoublyLinkedList.hpp on our Github. // // Doubly-linked list with 2 dummy nodes // #pragma once #include <stdexcept> template<typename T> struct Node { T data; Node<T>* next; Node<T>* prev; Node() = delete; // Intentionally no default constructor Node( const T & element ) : data( element ), next( nullptr ),...
C++ function to a string into enum function to a enum into a string check valid...
C++ function to a string into enum function to a enum into a string check valid / loop ( asking a couple of times until answer invaild) For example Enum Fruit ( APPLE, STRAWBERRY, BLUEBERRY, BLACKBERRY) output should be what is your favorit fruit? : strawberry you will have STRAWBERRY. what is your favorite fruite : peach invaild TIA
Implement a substring search function which searches a string for a given keyword. The function should...
Implement a substring search function which searches a string for a given keyword. The function should return a pointer to the address of the first character of the substring within the original string, if it exists. If the substring is not found within the string, return NULL. If the input is found, print "Key found." otherwise print "Key not found." Your program’s input and output should match what is seen in the example run. • You may only use stdio.h....
C Programming Language Please Given a string S and keyword string K, encrypt S using keyword...
C Programming Language Please Given a string S and keyword string K, encrypt S using keyword Cipher algorithm. Note: The encryption only works on alphabets. Numbers and symbols such as 1 to 9, -, &, $ etc remain unencrypted. Input:     Zombie Here     secret     where: First line represents the unencrypted string S. Second line represents the keyword string K. Output:     ZLJEFT DTOT Explanation: We used "secret" keyword there. Plain Text: A B C D E F G H I J K...
1 Question 1 Suppose a tweet is represented as a tuple (tweet id, Boolean, Boolean). The...
1 Question 1 Suppose a tweet is represented as a tuple (tweet id, Boolean, Boolean). The second Boolean element will be True if the tweet matches Twitter REST API Query otherwise, it will be False. The third Boolean element will be True if the tweet content is positive (relevant to the topic of interest); Otherwise, it will be False. The whole twitter space is represented as the set of tweets: Suppose the crawled tweets using Twitter REST API Query is...
Write a function that takes a C string as an input parameter and reverses the string.
in c++ Write a function that takes a C string as an input parameter and reverses the string. The function should use two pointers, front and rear. The front pointer should initially reference the first character in the string, and the rear pointer should initially reference the last character in the string. Reverse the string by swapping the characters referenced by front and rear, then increment front to point to the next character and decrement rear to point to the...
Sort a string array by frequency given an array of string write a function that will...
Sort a string array by frequency given an array of string write a function that will return an array that is sorted by frequency example {"hello","hola","hello","hello","ciao","hola","hola","hola"} returned array should be {"hola","hello","ciao"}
Write a function that counts the colors in a string using JavaScript. String "The quick brown...
Write a function that counts the colors in a string using JavaScript. String "The quick brown fox jumped the blue fence and landed on red paint." This should return the number of colors. The colors you are looking for are "blue, green, brown, gray, black, brown, red, purple".
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT